Can SUMPRODUCT do OR statements?

[tangcla]

So, let's say my basic SUMPRODUCT formula starts off like this:

SUMPRODUCT($G$35:$G$3082*($D$35:$D$3082="OP")

Can I get it so that the SUMPRODUCT gives me results for criteria in which D35-D3082 is, say, OP and OT and OZ?

[shg]

Let's say you try SUMPRODUCT($G$35:$G$3082 * ($D$35:$D$3082 = {"OP","OT","OZ"})

[tangcla]

Let's say you're awesome.

[shg]

Well, let's say thank you

[Richard Schollar]

Hi

An alternative approach which is more efficient is:

SUMPRODUCT($G$35:$G$3082*(ISNUMBER(MATCH($D$35:$D$3082,{"OP","OT","OZ"},0)))

The efficiency gain comes from retaining a 1 dimensional array in the final output passed to Sumproduct. I suspect that any real world benefit is questionable unless you are using really big ranges though, but it's always good to know of a few ways to do things.

Richard

[tangcla]

Well, let's say thank you

Let's say I do

thank you I forgot to say it before

[tangcla]

Hi

An alternative approach which is more efficient is:

SUMPRODUCT($G$35:$G$3082*(ISNUMBER(MATCH($D$35:$D$3082,{"OP","OT","OZ"},0)))

The efficiency gain comes from retaining a 1 dimensional array in the final output passed to Sumproduct. I suspect that any real world benefit is questionable unless you are using really big ranges though, but it's always good to know of a few ways to do things.

Richard

There's so many formulas in Excel, so much unharnessed power... but let me try and master sumproduct first hehe

[rylo]

Hi

Merely to add to the confusion with another syntax approach.....

SUMPRODUCT((G35:G3082),(D35:D3082="OP")+(D35:D3082="OT")+(D35:D3082="OZ"))


rylo

[Colin Legg]

In case anyone is interested..... I took a little time to test the calculation speeds of these formulas by measuring the calculation time of a worksheet containing 800 of each formula.

The average results are below. If anyone else tests them, then the ratios should be similar. I'd have to test more iterations to get the results absolutely reliable, but I think they provide a good indicator as they stand.

Formula 1 ---- Average Speed 2.3375 secs
=SUMPRODUCT($G$33:$G$3080*(ISNUMBER(MATCH($D$33:$D$3080,{"OP","OT","OZ"},0))))

Formula 2 ---- Average Speed 2.835938 secs
=SUMPRODUCT($G$33:$G$3080*($D$33:$D$3080={"OP","OT","OZ"}))

Formula 3 ---- Average Speed 2.8625 secs
=SUMPRODUCT(($G$33:$G$3080),($D$33:$D$3080="OP")+($D$33:$D$3080="OT")+($D$33:$D$3080="OZ"))

Formula 4 ---- Average Speed 2.875 secs *This formula is CTRL+SHIFT+ENTER entered....
=SUM($G$33:$G$3080*($D$33:$D$3080={"OP","OT","OZ"}))


So Richard is correct: his version (formula 1) is more efficient, perhaps at a cost of being slightly more complex(?), whilst the other solutions are quite similar.

That said, I was able to improve its efficiency further by coercing the boolean result array into a numerical result array using negation, and then passing two numerical arrays into Sumproduct and letting it multiply/sum them together, and then converting the numerical result returned from the function into a postive number using negation again. So my proposed solution is this variation:

Formula 5 ---- Average Speed 1.84375 secs
=-SUMPRODUCT($G$33:$G$3080,-(ISNUMBER(MATCH($D$33:$D$3080,{"OP","OT","OZ"},0))))

It's a pretty big time save over formula 1 but, again, the compromise is that it is more complicated...


Hope that's of interest...
Colin

[rylo]

Colin

Found that intesting.

On formula 5, can you test with the forced coercion in this construct

=SUMPRODUCT($G$33:$G$3080,--(ISNUMBER(MATCH($D$33:$D$3080,{"OP","OT","OZ"},0))))

I doubt that it would be any better, but to me it makes the formula more consistent.

From Colin_I via PM.....

Basically, in terms of efficiency:
-SUMPRODUCT({Numbers},-{Booleans}) is faster than
SUMPRODUCT({Numbers},--{Booleans}) is faster than
SUMPRODUCT({Numbers}*{Booleans})

rylo

[shg]

Let's say you're awesome.

Well, let's say thank you

Let's say I do. Thank you, I forgot to say it before

What I meant was thank you for the compliment -- it wasn't a nudge for more appreciation.

Thanks for posting the follow-up.

[tangcla]

haha, I'm confused now I think I'll stick with what I had.

[daddylonglegs]

There are some other potential differences. Any text in a single cell in the sum range G33:G3080 [even a "formula blank"] will cause formulas 1, 2 and 4 to fail, 3 and 5 won't.

I presume this is part of a larger SUMPRODUCT formula but, if not, this might be as quick

=SUM(SUMIF($D$35:$D$3082,{"OP","OT","OZ"},$G$35:$G$3082))

[tangcla]

There are some other potential differences. Any text in a single cell in the sum range G33:G3080 [even a "formula blank"] will cause formulas 1, 2 and 4 to fail, 3 and 5 won't.

I presume this is part of a larger SUMPRODUCT formula but, if not, this might be as quick

=SUM(SUMIF($D$35:$D$3082,{"OP","OT","OZ"},$G$35:$G$3082))

yes it's part of a bigger sumproduct (there's three nested conditions).

I noticed that an error like #N/A will cause the formula to fail, however I did an IF(ISERROR(vlookup on it so that it would return a blank value, and then it works.

[daddylonglegs]

You might be able to make the formula work with #N/As ignored.

For instance, with a version of Richard's suggestion,

=SUMPRODUCT($G$33:$G$3082,--ISNUMBER(MATCH($D$33:$D$3082,{"OP","OT","OZ"},0)))

#N/As in D33:D3082 won't stop you getting a result

This formula will allow #N/As in either range

=SUM(IF(ISNUMBER(MATCH($D$33:$D$3082,{"OP","OT","OZ"},0)),IF(ISNUMBER($G$33:$G3082),$G$33:$G3082)))

confirmed with CTRL+SHIFT+ENTER

Although, of course, you might prefer an error if you want to be alerted to #N/As when you didn't know they were there

[tangcla]

Hello, time to dig up an old thread - daddylonglegs, in your last post you mentioned about a SUMPRODUCT formula which will allow errors and blanks.

Can you please elaborate on how one constructs a formula with this? I now need a formula for this!