Sum row/cell values when multiple column conditions are fulfilled

Question

I'm want to sum every row/cell range in a table where one of (multiple) conditions is fulfilled in one of the columns.

An example:

I want to find the sum of all cells where the two (or more) conditions are met - in the example above it would mean the sum of C2 and D2 (3) plus the sum of C4 and D4 (5), bringing the total to 8.

Answer 1

when advanced function fails.. build one. [ my manual sumifs ] :

=SUMIF(B2:B5,"a",C2:C5)+SUMIF(B2:B5,"c",C2:C5)+SUMIF(B2:B5,"a",D2:D5)+SUMIF(B2:B5,"c",D2:D5)

Hope it helps..

Answer 2

You can use this formula to get the total:

=SUMPRODUCT(--(($A$2:$A$5=$A$7)+($A$2:$A$5=$B$7))*($B$2:$C$7))

N.B. Cell A7 & B7 carries criteria.

Or, you may use this also:

=SUM(SUMIFS(B2:B7,A2:A7,{"a","c"})+SUMIFS(C2:C7,A2:A7,{"a","c"}))

Note: Adjust the cell references as needed.