CODEWITHSUNDEEP
regexfiltergoogle-sheetssumgoogle-sheets-formula
user1491636
user1491636

Reputation: 2436

Conditional sum across multiple columns

Let's say I have columns A, B, C, E and I want to sum the numbers in E (range of rows) only if the value in E is not empty and matches a value in either A or B or C of the corresponding row. For example, in the sample below, A4=E4 and B7=E7, so we sum E4 + E7.

enter image description here

The only way I know how to solve this is to use two separate operations; a conditional if function which outputs matching values to another column and then a summation function to sum the entire column. I'd like to solve this with a single operation if possible.

Upvotes: 2

Views: 227

Answers (1)

player0
player0

Reputation: 1

=SUM(FILTER(E1:E9, 
 REGEXMATCH(""&E1:E9, ""&A1:A9*1)+
 REGEXMATCH(""&E1:E9, ""&B1:B9*1)+
 REGEXMATCH(""&E1:E9, ""&C1:C9*1)))

0

=ARRAYFORMULA(SUMPRODUCT(IFERROR(IFERROR(
 REGEXEXTRACT(" "&TRIM(TRANSPOSE(QUERY(TRANSPOSE(A1:C9),,999^99)))&" ", " "&E1:E9&" "), 
 REGEXEXTRACT(" "&TRIM(TRANSPOSE(QUERY(TRANSPOSE(A1:C9),,999^99)))&" ", " "&F1:F9&" ")))))

0

Upvotes: 2

Related Questions