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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jim_0068 - 13 Jun 2006 19:47 GMT

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

Reply to this Message

Don Guillett - 13 Jun 2006 20:35 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
> I have learned how to use both a sumproduct formula and an array formula
[quoted text clipped - 7 lines]
>
> Thanks

Reply to this Message

Bob Phillips - 13 Jun 2006 20:59 GMT

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)

> I have learned how to use both a sumproduct formula and an array formula
> to both "count" and "sum" based on multiple criteria.
[quoted text clipped - 12 lines]
> jim_0068's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32822
> View this thread: http://www.excelforum.com/showthread.php?threadid=551604

Reply to this Message

jim_0068 - 13 Jun 2006 22:17 GMT

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

Do you have an opinion on the matter

Reply to this Message

Bob Phillips - 13 Jun 2006 22:54 GMT

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.

Signature

HTH

Bob Phillips

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

> Sorry, don't have the cash to fly over for an excel conference.
>
> Do you have an opinion on the matter?

Reply to this Message

Arvi Laanemets - 14 Jun 2006 12:04 GMT

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

:-))

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> I have learned how to use both a sumproduct formula and an array formula
> to both "count" and "sum" based on multiple criteria.
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message