PDA

View Full Version : Sumproduct vs Ctrl+Shift+Enter arrays



Sir Babydum GBE
03-10-2008, 06:03 AM
Hi

I use sumproduct a lot now when I need a query with multiple lookups. I learnt that here (thanks).

The other guys in my team had never heard of sumproduct (not for that purpose anyhow) and always used Ctrl+Shift+Enter arrays to get the same result.

What are the pros and cons of sumproduct vs other array types?

Bob Phillips
03-10-2008, 06:10 AM
SUMPRODUCT doesn't need Ctrl-Shift-Enter (a big plus).

SUMPRODUCT is marginally more efficient.

There are some things that cannot be done with SUMPRODUCT. For instance, if you have a range that could have errors, to get the sum of values above 0 you would think you could use

=SUMPRODUCT(--(NOT(ISERROR(A1:A20))),--(A1:A20>0),A1:A20)

but all A1:A20 will register any errors, so the whole formula errors. But this array formula works fine

=SUM(IF(NOT(ISERROR(A1:A20)),IF(A1:A20>0,A1:A20)))

Get them all to read http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.

Sir Babydum GBE
03-10-2008, 06:30 AM
Thanks - and an excellent resource, by the way.

Bob Phillips
03-10-2008, 06:55 AM
Thanks - and an excellent resource, by the way.

It's the best, no ands, no ifs, no buts :)

matthewspatrick
03-10-2008, 04:58 PM
It's the best, no ands, no ifs, no buts :)

All kidding aside, it is the explanation that I refer everyone to. :thumb