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

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

How about =SUMPRODUCT(--((A1:B10)={"Ford","Chrysler"})) ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"avi" wrote in message
...
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

Thanks, but... too easy

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

Avi

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")



--
---
HTH

Bob

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



"avi" wrote in message
...
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

Maybe this example will more clarify my question

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


Thanks
Avi

=COUNTIF(A1:A10,"Ford")+COUNTIF(Z7:AQ17,"Ford")
as Bob told you
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"avi" wrote in message
...
Maybe this example will more clarify my question

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


Thanks
Avi

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