Multiple Criteria parameters in SUMIFS

[haissk]

I have a below table as below
A B C
a aa 1
a aa 1
a dd 1
a aa 1
b aa 1
b bb 1
b aa 1
b bb 1
c cc 1
c bb 1
c bb 1
c cc 1
d cc 1
d aa 1
d bb 1
d cc 1

When i put the formula


=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"}))

it returns 12 However when i put


=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,{"aa","bb"}))

it returns only 5

Can one one help me with this. I dont wnat to use multi formula like

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"aa"})) + SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"bb"}))

I want to achieve this using SUMIFS only

[daddylonglegs]

If you have two "array constants" (the {"a","b","c"} and {"aa","bb"} parts) then one needs to be separated by commas and the other by semi-colons (assuming UK settings), e.g.

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,{"aa";"bb"}))

[haissk]

Wow!!! Thank you so much daddylonglegs..

instead of hardcoding the criteria, can i store it in excel cells and pass that to the formula?

Also how to do if i have 3rd Criteria. I tried =SUMPRODUCT(SUMIFS(D1:D16,A1:A16,{"a","b","c"},B1:B16,{"aa";"bb";"cc"},C1:C16,{"zz";"yy"}))
but did not work

[daddylonglegs]

Yes, but similar to before one needs to be a column and one a row, e.g.

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,E1:G1,B1:B16,H2:H3))

If both criteria ranges are columns (or both rows) then you need to TRANSPOSE one like this

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,TRANSPOSE(J2:J4),B1:B16,H2:H3))

....and when using TRANSPOSE the formula needs to be "array entered", i.e. confirm with CTRL+SHIFT+ENTER

[haissk]

Also how to do if i have 3rd Criteria. I tried =SUMPRODUCT(SUMIFS(D1:D16,A1:A16,{"a","b","c"},B1:B16,{"aa";"bb";"cc"},C1:C16,{"zz";"yy"})) but did not work

[daddylonglegs]

Sorry, I didn't see that part - 3rd criteria isn't possible with this setup - for 3 or more switch to SUMPRODUCT with MATCH, e.g.

=SUMPRODUCT(D1:D16,ISNUMBER(MATCH(A1:A16,{"a","b","c"},0)+MATCH(B1:B16,{"aa","bb","cc"},0)+MATCH(C1:C16,{"zz","yy"},0))+0)

You can add more criteria within that syntax