# Sumif/sumproduct with a range as a criteria

1. ## Sumif/sumproduct with a range as a criteria

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  Register To Reply

2. ## Re: Sumif/sumproduct with a range as a criteria

How's this?

forum example ah.xlsx  Register To Reply

3. ## Re: Sumif/sumproduct with a range as a criteria

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)  Register To Reply

4. ## Re: Sumif/sumproduct with a range as a criteria

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.  Register To Reply

5. ## Re: Sumif/sumproduct with a range as a criteria

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  Register To Reply

6. ## Re: Sumif/sumproduct with a range as a criteria

Or use a pivot table  Register To Reply

7. ## Re: Sumif/sumproduct with a range as a criteria  Register To Reply

8. ## Re: Sumif/sumproduct with a range as a criteria

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  Register To Reply

9. ## Re: Sumif/sumproduct with a range as a criteria

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"))  Register To Reply

10. ## Re: Sumif/sumproduct with a range as a criteria

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

There are currently 1 users browsing this thread. (0 members and 1 guests) 