SUMIFS with OR in criteria

**SergSlim** · 08-31-2012, 07:59 AM

Hello.

I came across a problem today and haven't found an easy way to solve it.
I have this formula:

=SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,Sheet2!I4)+SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,Sheet2!I5)+SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,Sheet2!I6)+SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,Sheet2!I7)

I'd like to make this formula smaller by doing something like this:

=SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,OR(Sheet2!I4,Sheet2!I5,Sheet2!I6,Sheet2!I7))

But it doesn't work this way. Can I make something similar that will work?

**daddylonglegs** · 08-31-2012, 08:21 AM

Try this version

=SUMPRODUCT(SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,Sheet2!I4:I7))

If you use the 4 cell range as the criteria the SUMIFS actually returns an "array" of 4 values.....so SUMPRODUCT is just used here to sum that array. Note with only one criteria range you could also use SUMIF

**SergSlim** · 08-31-2012, 08:58 AM

Thanks, it does work.
However I investigated this a bit more, and came to the same result by entering just =SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,Sheet2!I4:I7)
but as an array, by pressing CTRL+Shift+Enter after entering the formula.

I don't have a lot of data in that file so far to fully test if it works as supposed.

**daddylonglegs** · 08-31-2012, 09:20 AM

Originally Posted by SergSlim

=SUMIFS('Sheet 1'!H:H,'Sheet 1'!E:E,Sheet2!I4:I7)

I don't believe that will work, array entered or not - what you'll get is just the sum for the first criterion, i.e. where the match is with Sheet2!I4, as per my reply you need to wrap the formula in a SUMPRODUCT function (or a SUM function and "array enter")

**SergSlim** · 08-31-2012, 09:29 AM

Yeah, actually you are right. It just happened that I tested it with data that was in I4. Changed it to I5 - and it showed 0. In that case I'll go with your proposed version. Thanks again.