CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulasumifs
greysqrl
greysqrl

Reputation: 977

SUMIFs multiple criteria is not working consitently

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

Upvotes: 1

Views: 392

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

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

Upvotes: 3

BigBen
BigBen

Reputation: 49998

Use SUMPRODUCT:

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

enter image description here

Upvotes: 2

Related Questions