SUMIF Formula including OR

[garymu16]

Dear All,

How do I modify my formula (below) to include an OR statement?

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

I need something like this:

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

I know this will no-doubt be embarassingly easy but I can't get my head around it.

Thx in advance
Gary

 

[Andrew Poulsom]

How about?

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

 

[Yard]

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

 

[Jon von der Heyden]

or:

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

 

[Colin Legg]

yet another.... or:

=SUM(SUMIF(I4:I20,{"Core","Existing"},G4))

 

[Jon von der Heyden]

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.

 

[Colin Legg]

Hi Jon

This bit:

SUMIF(I4:I20,{"Core","Existing"},G4)

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:

MyArray ={"Core","Existing"}

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:

=SUM(SUMIF(I4:I20,MyArray,G4))   'CSE entered
'or
=SUMPRODUCT(SUMIF(I4:I20,MyArray,G4))

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

 

[barry houdini]

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)

 

[Jon von der Heyden]

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.

 

[Colin Legg]

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

=SUM(SUMIF(I4:I20,{"Core","Existing"},G4))

vs

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

?



I know that

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

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