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 !

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.

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

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...