SUMPRODUCT array formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

avi - 20 Nov 2007 22:00 GMT

Hello,

I was referred by Bob Philips to to the SUMPRODUCT array formula
method which works incredibly fast. But it look as if the formula does
not work with non-contiguous ranges, for exemple:-

=SUMPRODUCT(--((A1:A10,B1:B10)={"Ford","Chrysler"}))

Any help will be appreciated

Avi

Reply to this Message

Bernard Liengme - 20 Nov 2007 22:37 GMT

Firstly SUMPRODUCT is not an array formula. Just commit it with a simple
ENTER

How about =SUMPRODUCT(--((A1:B10)={"Ford","Chrysler"})) ?
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hello,
>
[quoted text clipped - 7 lines]
>
> Avi

Reply to this Message

avi - 20 Nov 2007 22:42 GMT

Thanks, but... too easy

Actually the non contiguous range could be any range that the user
picks

Avi

Reply to this Message

Bob Phillips - 20 Nov 2007 22:46 GMT

It is not my SUMPRODUCT formula, it is MS', I just wrote a paper about it.

What is the range that you are checking? SP is usually used to check 2 or
more conditions. If you just want to check A1:B10 for two differing values,
I would use

=COUNTIF(A1:B10,"Ford")+COUNTIF(A1:B10,"Chrysler")

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello,
>
[quoted text clipped - 7 lines]
>
> Avi

Reply to this Message

avi - 20 Nov 2007 22:56 GMT

Maybe this example will more clarify my question

=SUMPRODUCT(--((A1:A10,Z7:AQ17)={"Ford"}))

Thanks
Avi

Reply to this Message

Bernard Liengme - 21 Nov 2007 01:56 GMT

=COUNTIF(A1:A10,"Ford")+COUNTIF(Z7:AQ17,"Ford")
as Bob told you

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Maybe this example will more clarify my question
>
> =SUMPRODUCT(--((A1:A10,Z7:AQ17)={"Ford"}))
>
> Thanks
> Avi

Reply to this Message

avi - 21 Nov 2007 07:07 GMT

The range is a named range that the user picks and it could consists
of many subranges that I do not know in advance.

What I am looking for is to give the formula the range name and not
the explicit address

But it seems that the formula will not work with a name representing
such a range

Thanks a lot
Avi

Reply to this Message