Sumif/sumproduct with a range as a criteria

[fukirua]

Hi guys. I ave been fighting with this for a while and will be really appreciated your help.
I need to get the sum amount IF
1. Store number matches X
2. Period matches Y
3. Account matches a range I2:I4

I cannot figure out how to use this range I2:I4 as a criteria for SUMIFS.
From another side I found out how to use 3rd criteria =SUMPRODUCT(SUMIF(C:C,I2:I4,E:E)). But I don't know how I can extract an array from this formula to merge with other conditions.

Please see the example in the attachment.forum example.xlsx

[Alex H]

How's this?

forum example ah.xlsx

[martindwilson]

to get the total in one go
=SUMPRODUCT((C2:C200=I2)+(C2:C200=I3)+(C2:C200=I4),--(B2:B200="store 1"),--(D2:D200=1.2012),E2:E200)

[fukirua]

Thank you Sir. But is there a way to replace this part (C2:C200=I2)+(C2:C200=I3)+(C2:C200=I4) with something straight-forward referring to a cell range? I will need to add some accounts or delete them. So I would prefer to have it more alterable.

[FDibbins]

If you need to make this scalable - add/remove accounts, try this. make a small table, with the accounts you want in I2 downwards. Then in J2, copied down, use this...

=IF(I2="","",SUMIFS($E$2:$E$14,$A$2:$A$14,12,$D$2:$D$14,1.2012,$C$2:$C$14,I2))
you can then just add up the values in J

[popipipo]

Or use a pivot table

[martindwilson]

perhaps use advanced filter and sub total instead

[fukirua]

Sorry if I misled you. It will definitely work for one store. I have hundreds of them so I cannot make sub-sum for each account for each store. How can I get an array {1,0,0,0,1,0,0,1,....} from comparing C:C to J:J? If C:C match any of J:J
Thank you

[daddylonglegs]

You can just extend your original example using SUMIFS instead of SUMIF, e.g.

=SUMPRODUCT(SUMIFS(E:E,C:C,I2:I4,B:B,"Store 1",D:D,"1.2012"))

[fukirua]

That's it! Thank you very much. I really appreciate it.
I just don't understand the logic of sumproduct(sumifs())