Sumproduct vs Ctrl+Shift+Enter arrays

[Sir Babydum GBE]

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]

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]

Thanks - and an excellent resource, by the way.

[Bob Phillips]

Thanks - and an excellent resource, by the way.

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

[matthewspatrick]

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

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