SUMPRODUCT array formula

[avi]

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

 

[Bernard Liengme]

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

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

 

[avi]

Thanks, but... too easy

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

Avi

 

[Bob Phillips]

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]

Maybe this example will more clarify my question

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


Thanks
Avi

 

[Bernard Liengme]

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

 

[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