SUMIF Formula including OR

How about?

=SUMIF(I4:I20,"Core",G4:G20)+SUMIF(I4:I20,"Existing",G4:G20)

Simple way:

=SUMIF(I4:I20,"Core",G4:G20)+SUMIF(I4:I20,"Existing",G4:G20)

Another way:

=SUM(IF(OR(I4:I20="Core",I4:I20="Existing"),G4:G20,0))
entered using Ctrl+Shift+Enter

Probably about 10 other ways as well....

or:

=SUMPRODUCT((I4:I20="core")+(I4:I20="existing"),G4:G20)

yet another.... or:

Colin

I understand this similar method:

=SUMPRODUCT(SUMIF(I4:I20,{"core";"existing"},G4:G20))

But your formula baffles me.
Why does it work?

The Evaluate Formula does little to explain it.

Hi Jon

This bit:
Gives a horizontal array of 2 totals, one has the SUM of all the "Core"s, the other has the SUM of all the "Existing"s. So something like {5,10}

Then SUM() adds them together to give 15.

Since {"Core","Existing"} is an inline array constant (ie. containing directly within the formula), the formula does not have to be CSE entered. (My definition would be that this is already an "Array Formula".)

Conversely, if you were to use a named array constant, MyArray, where MyArray is defined as:
Then this formula would have to be CSE entered, I think because 'MyArray' is like a pointer to the names table, you have to tell Excel to treat it like an array:


So then comes the $1M question... which formula is best here? My guess would be Andrew's =SUMIF(I4:I20,"Core",G4:G20)+SUMIF(I4:I20,"Existing",G4:G20). But if lots of conditions then it becomes unwieldly - I've not tested efficiency between yours and mine, I suspect =SUMPRODUCT((I4:I20="core")+(I4:I20="existing"),G4:G20) might just edge it, but I'm not sure without running some tests. One of the formula experts here will know.

HTH
Colin

What you don't mention, Colin, is that you specified the "sum range" as just G4.

SUMIF always uses a SUM range the same size as the first range, the top left cell of the sum range used will be the same as the top left of the range actually specified. That means the same range is summed even if you specifiy the range as G4, G4:G10000 or even G4:IV4.

Edit: this version could be a contender in the speed stakes.....

=SUMPRODUCT(--ISNUMBER(MATCH(I4:I20,{"core","existing"},0)),G4:G20)

Hi Colin

Thanks for your explanation.

So from my observation Excel will know to handle the entire 'sum' array size even though you have only made reference to a single cell in the sum range (G4). So that's something new to me. Thanks.

Your explanation of using inline array completes the explanation for me. I have repeatedly used the SUMPRODUCT method (as per my previous post) despite using inline array. So now I know I can simply use SUM.

Thanks again!

Edit: Just noticed Barry's post. Thanks for your explanation on the sum range.

Very true, Barry, I was concentrating too hard on explaining why it doesn't need to be CSE entered or need to be wrapped by SUMPRODUCT(). Speaking of formula experts, do you have an opinion on
vs
?



I know that
will be slower than Jon's SUMPRODUCT() formula using +, although again it is more easily extendable if the conditions increase.