SUMIFS with multiple possibilities for 2 criterias

[drom]

SUMIFS with multiple possibilities for one criteria, can be solved using:

=SUMPRODUCT(SUMIFS(G10:G100,A10:A100,A5,B10:B100,B5,C10:C100,C3:C6))

According to:
https://www.mrexcel.com/board/index.php?threads/821600/

But If I need to apply SUMIFS with multiple possibilities for 2 criterias
=SUMPRODUCT(SUMIFS(G10:G100,A10:A100,A5,B10:B100,z5:z9,C10:C100,C3:C6))

Is not working in my Book
What am I missing

 

[steve the fish]

Try like this. Two is your limit though.

=SUM(SUMIFS(G10:G100,A10:A100,A5,B10:B100,Z5:Z9,C10:C100,TRANSPOSE(C3:C6)))

Enter CTRL-SHIFT-ENTER.

 

[Aladin Akyurek]

Try to enter either z5:z9 or C3:C6 horizontally. Let's C3:C6 map onto C3:E3. Then we can have:

=SUMPRODUCT(SUMIFS(G10:G100,A10:A100,A5,B10:B100,z5:z9,C10:C100,C3:E3))

Otherwise, try:

=SUMPRODUCT(G10:G100,--(A10:A100=A5),--ISNUMBER(MATCH(B10:B100,Z5:Z9,0)),--ISNUMBER(MATCH(C10:C100,C3:C6,0)))