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!

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!

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.
--
Kevin Vaughn


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

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)

wrote in message
ups.com...
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!