Excel SUMIFS formula not working

Question

This is a simplified example of my problem:

I have two columns: category and hours. I want to find the sum of hours for A and C. The answer should be 5.1, but it shows 0. If I use SUMIF formula just for A or C, then it works, but if I use it for both A and C, it shows 0. What am I doing wrong?

Answer 1

I suspect that you want:

=SUMPRODUCT(SUMIFS(B2:B7, A2:A7, D2:D3))

That sums values in B2:B7 when the corresponding row in A2:A7 equals the value in D2 __or__ D3.

Your SUMIFS sums values in B2:B7 when the corresponding row in A2:A7 equals the value in D2 __and__ D3.  Obviously, a value in A2:A7 cannot equal __both__ at the same time.

My formula above works only if the cells that A2:A7 should equal are contiguous, like D2 and D3.  Suppose you want to compare with D2 and F4.  More generally:

=SUMPRODUCT(SUMIFS(B2:B7, A2:A7, CHOOSE({1,2}, D2, F4)))

Answer 2

Your formula's criteria is working like the AND clause. Something cannot be both A and C. You need an OR clause.

You can hard code the string array and sum the result like this,

=SUM(SUMIFS(B:B, A:A, {"a","c"}))

You can use a range of cells for criteria if they are in a different 'direction' than your other ranges. Since B2:B7, A2:A7 and D2:D3 are all in rows, we can TRANSPOSE D2:D3 and enter it as an array formula with Ctrl+Shift+Enter.

=SUM(SUMIFS(B2:B7, A2:A7, TRANSPOSE(D2:D3)))

Answer 3

Thanks a lot! It works.

Answer 4

Hi,

In cell D9, enter this formula

=SUMPRODUCT((ISNUMBER(MATCH($A$2:$A$7,$D$2:$D$3,0)))*($B$2:$B$7))

Hope this helps.