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

Dave
--
Brevity is the soul of wit.


" wrote:

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

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))
DOESN'T WORK <<<

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!