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

 

[Colin Legg]

When I did the testing, the referenced ranges were huge and I think I used 100 formulas in the sheet. So if you only have 1 formula which references a range of 100 cells you really wouldn't notice any difference.

The thing I really took from this is that using:

=SUM(SUMIF($A$2:$A$30000,{"Word1";"Word2";"Word3";"Word4";"Word5"},$B$2))
 
or, if you are using a range list:
 
=SUMPRODUCT(SUMIF($A$2:$A$30000,List_Range,$B$2:$B$30000))

seem to be a very nice variations instead of using
=SUMIF + SUMIF + SUMIF + SUMIF....

because it shortens the formula such a huge amount and with practically no overhead.

 

[DonkeyOte]

Nothing to add, just thought I'd pipe up and say thanks to Colin & co. as I found this an interesting thread ... and have tucked away for future reference

Like Jon I wish I knew how/why/when the mechanics behind one formula would be more efficient than another... maybe one day...

For one conditional SUM with a variable criteria value I have in the past used the SUM(SUMIF(...,{ },...)) approach ... and in a way I'm glad that I've not been using a hideously inefficient approach

 

[Colin Legg]

I was just reading this old thread and noticed that I posted a formula in post #5 which had some discussion in posts #6-#9:

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

This formula isn't very good because the size of sum_range (G4) doesn't match range (I4:I20). This means that the formula is volatile.
It should be written as:

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