SUMIFs multiple criteria is not working consitently

1
votes

I am using Google Sheets with the spreadsheet shown below.

I want to Sum the 'Amount' column IF the Key in column J == the key in column B AND The Assigned person == the actual person.

So, where the key is 2, we'd have a subset of 7 items. From that the assigned person is Sally and four entries match, our total would therefore be the sum of those matching values which are 20, 10, 2, 4 giving a sum of 36.

In K3, we can correctly see the sum of 36.

The formulae I used in that cell is: =SUMIFS(H:H,B:B,J3,G:G,D:D)

The cell below has the formulae: =SUMIFS(H:H,B:B,J4,G:G,D:D)

So, that should, I believe sum the values 3,8 and 4 since the key (3) in column J matches three items in column B. In each case Mike is the assigned and actual person, which means we should be summing 3, 8 and 4. However, the value as you can see is 0.

Any ideas what I'm doing wrong, please?

enter image description here

2
google-sheetsgoogle-sheets-formulasumifs
Change D:D to D3, D4, etc. – BigBen
I don't think that would work because, when we use the key with a value of '3' I can't say what the matching cell is, i.e. it's not D3 it's D10, whereas the 'key' is in J4. – greysqrl
Sorry, misread the question, disregard. – BigBen
No problem. Thank you anyway :) – greysqrl
Use SUMPRODUCT. – BigBen

2 Answers

3
votes

You can also do this with a single formula in Google Sheets;

=query(B2:H," select B,sum(H) where D=G and B is not null group  by B label sum(H) ''")

enter image description here

2
votes

Use SUMPRODUCT:

=SUMPRODUCT((B$2:B$13=J2)*(D$2:D$13=G$2:G$13)*H$2:H$13)

enter image description here