Search code examples
excelsumifs

How to use SUMIFS function for summing values if criteria range exists in a range?

My data:

A   B    C
a   3    d
b   1    a
c   8    e
d   5

I want to use SUMIFS function, sum range is B1:B4, if the corresponding value in column A exists in column C, sum the values in column B, in this case the sum will be 3 + 5 = 8 I tried =SUMIFS(B1:B4,A1:A4, COUNTIF($C$1:$C$3,A1) but I get zero.

Solution 
  • =SUMPRODUCT(SUMIF(A1:A4,C1:C3,B1:B4))

    Link for further reading: COUNTIFS: Multiple “OR” Criteria For One Or Two Criteria_Ranges