Using SUMPRODUCT with criteria - OR functionality

[ceecld]

Earlier on today some of you guys gave me some good tips on using SUMPRODUCT to perform criteria based array functions. previously i had used array formulas with SUM and IF statements, however these appear to be alot slow on the scales im using them for.

My question relates to using the SUMPRODUCT function with an OR criteria. lets say we want to sum column C based on a conditional statement including both col A and B, e.g. sum C1:10 where A1:A10="motor" OR B1:B10="continuous"

=sumproduct((A1:A10="motor")+(B1:B10="continuous"),(C1:C10))

now this formula would work perfect if both conditions were not encountered in the same row. In the event that it does happen though you encounter a double count. as both conditons will give a TRUE result and the addition places a 2 in the array - doh! So is there a function i can use to limit the value of (A1:A10="motor")+(B1:B10="continuous") to 1 so that this unwanted double count doesn't happen?

cheers

 

[AnotherHiggins]

The first thing that comes to mind is to just subtract the count of rows where both conditions are met. Something like this:

=SumProduct((A1:A10="motor")+(B1:B10="continuous")*(C1:C10)) - SumProduct((A1:A10="motor")*(B1:B10="continuous")*(C1:C10))


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ceecld]

okay that would make sense, i thought there might have been a neater way to do it.

Thanks all the same

 

[GlennUK]

Hi John ( anotherhiggins ),

I think your formula needs one extra set of brackets :

=SUMPRODUCT([b]([/b](A1:A10="motor")+(B1:B10="continuous")[b])[/b]*(C1:C10)) - SUMPRODUCT((A1:A10="motor")*(B1:B10="continuous")*(C1:C10))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ceecld]

is there a computation advantage using sumproduct instead of an array forumla with sum and if statements, e.g.

{=sum(if((A1:A10="motor")+(B1:b10="cont"),C1:C10))}

i have massive spreadsheets which are colating data from many sources and some of them can take tens of seconds to calculate (using the array formula method) would it improve the computation efficiency to change them to sumproducts?

cheers