SUMPRODUCT help

[robotman]

I'm trying to use the SUMPRODUCT trick to count occurrences of multiple
match criteria.

With simple conditions like checking if a cell in a range is >0, the
trick works fine.
Even with LEFT, MID functions it works fine...

A2 = 1st criteria
B2 = 2nd criteroa
B50:Q800 = range of cells to search

=SUMPRODUCT(--(LEFT($B$50:$Q$800,1)=TEXT($A2,"0")),--(MID($B$50:$Q$800,3,3)=LEFT($B2,3)))
What I can't get to work is if I want to use the FIND (or SEARCH) as
one of the criteria to see if each cell contain the search text:

=SUMPRODUCT(--(FIND($B2,$B50:$Q800)>0))
Are there only certain functions that work with the SUMPRODUCT trick?
Any Ideas on how to do a search text as part of a SUMPRODUCT criteria?

Thanks!

 

[Dave F]

Wrap SUMPRODUCT in an IF function: =IF(B2=[your criteria],SUMPRODUCT([your
criteria]),"")

Dave

 

[Kevin Vaughn]

The following appears to work:

=SUMPRODUCT(--ISNUMBER(FIND("blah",A1:A10)))

Note, I did not use your range or criteria, but I believe you were mainly
interested in the concept.

 

[Bob Phillips]

The FIND will return a number if it matches, an error if not. SO wrap it in
ISNUMBER to check if it is a number, and you'll get your TRUE/FALSE values
to coerce in SP.

--
HTH

Bob Phillips

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