# SUMIFS with a criteria range ?

1. ## SUMIFS with a criteria range ?

Hi guys,

I have the following formula that I did:

=SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "delivered")+SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "shipped")

As you can see I have exactly same thing in the first SUMIFS function, and the second one except for the "delivered" and "shipped" criteria. Now, what I would like to do is to create a range that would be used instead of a single criteria.

Actually, I have more than 20 criteria but I don't want to do 20 separate SUMIFS and then adding them. Best would be if I could select a range of criterias (I would like to be updating that list easily in the future).

I'd much appreciate !  Register To Reply

2. ## Re: SUMIFS with a criteria range ?

I think you can shorten the formula by using a Sumproduct instead of Sumifs but even the Sumproduct will require all criteria to be built into the formula. Here's an example.

=SUMPRODUCT((Sheet2!B:B=1)*(Sheet3!A:A=Sheet3!A5)*(Sheet2!X:X="LOL")*((Sheet2!R:R="Delivered")+(Sheet2!R:R="shipped")+(Sheet2!R:R="Pending"))*Sheet2!L:L)

I'm not the most experienced Excel user so I'm sure there's a better way to accomplish but this might work in the meantime.  Register To Reply

3. ## Re: SUMIFS with a criteria range ?

Thanks. Unfortunately it seems like this actually take more memory for my excel   Register To Reply

4. ## Re: SUMIFS with a criteria range ?

Try this untested formula

Formula:  `Please Login or Register  to view this content.`  Register To Reply

5. ## Re: SUMIFS with a criteria range ?

Thanks, this actually works better !

Last question (I found about this option today) - can I just simply name some range i.e "Range1" and then put it into the formula ?
like - =Sum(SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R,"Range1") because it doesn't seem to work...  Register To Reply

6. ## Re: SUMIFS with a criteria range ?

use SUMPRODUCT instead of SUM as the outermost function, in that case.  Register To Reply

7. ## Re: SUMIFS with a criteria range ? Originally Posted by vemix can I just simply name some range i.e "Range1" and then put it into the formula
Don't refer the named range within in Double Quotes.  Register To Reply