Using SUMPRODUCT or an Array to calculate formulas with multiple criteria?

[jim_0068]

I have learned how to use both a sumproduct formula and an array formul
to both "count" and "sum" based on multiple criteria.

However my question to all of you is, what advantage does an array hav
over sumproduct and vice versa.

Personally i don't see why you wouldn't use sumproduct in place of AN
array.

Thank

 

[Don Guillett]

Agreed. I try to use sumproduct when I can but some don't lend themselves to
that with a short formula such as average(if

 

[Bob Phillips]

If you attend Charles Williams' presentation at the UK Excel User Conference
in July, you might find a reason for preferring one over another.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[jim_0068]

Sorry, don't have the cash to fly over for an excel conference.

Do you have an opinion on the matter

 

[Bob Phillips]

I tend to agree with Don, as they both work on arrays and suffer the
performance overhead therein. Array formulae, array functions, it has the
same calculation path AFAICS. But I look forward to hearing what Charles has
to say as he is an acknowledged expert on Excel calculation and performance.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Arvi Laanemets]

Hi

When the result will be a number, then I prefer SUMPRODUCT, unless array
formula is much shorter and looking more nice.
When the result will be a string ... there are no options left, as you see
)