SUMPRODUCT help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

robotman@gmail.com - 18 Oct 2006 20:23 GMT

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!

Reply to this Message

Dave F - 18 Oct 2006 20:51 GMT

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

Dave

Signature

Brevity is the soul of wit.

> I'm trying to use the SUMPRODUCT trick to count occurrences of multiple
> match criteria.
[quoted text clipped - 20 lines]
>
> Thanks!

Reply to this Message

Kevin Vaughn - 18 Oct 2006 20:53 GMT

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.

Signature

Kevin Vaughn

> I'm trying to use the SUMPRODUCT trick to count occurrences of multiple
> match criteria.
[quoted text clipped - 20 lines]
>
> Thanks!

Reply to this Message

Bob Phillips - 18 Oct 2006 23:00 GMT

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.

Signature

HTH

Bob Phillips

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

> I'm trying to use the SUMPRODUCT trick to count occurrences of multiple
> match criteria.
[quoted text clipped - 6 lines]
> 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 <<<
>
[quoted text clipped - 8 lines]
>
> Thanks!

Reply to this Message