CODEWITHSUNDEEP
regexif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
super_cool_display_name
super_cool_display_name

Reputation: 85

Match a text value in a column and perform a function on values in another column that match

Using Google Sheets I'm attempting to perform a match of values in a particular column and then based on that column execute a function(SUM) on matching values in a different column.

I've tried LOOKUP and VLOOKUP but those are throwing errors, presumably because they are expecting to only return a single value in a given range and not perform the SUM that I am requiring.

=LOOKUP("[sometext]*", A2:A25, SUM(H2:H25))

Ideally, what I would like to happen is to search the range A2:A25, find any rows that match "sometext*", e.g. "sometext1", "sometext2", "sometext3" etc. and then move over to second range and sum the values in the matched rows, e.g. "1", "2", "3" and return "6".

Upvotes: 1

Views: 507

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(SUM(IF(REGEXMATCH(A2:A25, "sometext"), 
 REGEXREPLACE(A2:A25, "\D+", )*1, )))

0

=ARRAYFORMULA(SUM(IF(REGEXMATCH(A2:A25, "sometext"), B2:B25, )))

r updated

Upvotes: 2

Related Questions