sum product not returning correct number
 

OK. So now I tried to sumproduct two other columns using the same
formula...and changing the column letters to match the new count I am looking
for - but it always returns a zero even though there is data there.
I want to find how many kids in each district have been evaluated for OI,
OHI, TBI, and Autism.
The District names (like "Camas") are in column E, rows 2 through 200
The Categories (OI, OHI, etc) are in column J, rows 2 through 200
I tried =SUMPRODUCT((E2:E200="Camas")*(J2:J200="TBI"))
Didn't work.
Any suggestions?
Thanks

sum product not returning correct number
 

On Thu, 27 Sep 2007 17:48:00 -0700, Grams
wrote:

OK. So now I tried to sumproduct two other columns using the same
formula...and changing the column letters to match the new count I am looking
for - but it always returns a zero even though there is data there.
I want to find how many kids in each district have been evaluated for OI,
OHI, TBI, and Autism.
The District names (like "Camas") are in column E, rows 2 through 200
The Categories (OI, OHI, etc) are in column J, rows 2 through 200
I tried =SUMPRODUCT((E2:E200="Camas")*(J2:J200="TBI"))
Didn't work.
Any suggestions?
Thanks

One likely explanation is that your cells don't contain exactly what you think
they do.

It may be that either the district, or the disease, also contain either
<space's or some other non-printing character.

If cleaning up the table is not possible, then you could try this formula which
uses wildcards to search for the information. You would have to ensure that
using a wildcard won't detect contents you don't want.

=SUMPRODUCT(ISNUMBER(SEARCH("*camas*",E2:E200))*IS NUMBER(SEARCH("*tbi*",J2:J200)))
--ron

sum product not returning correct number
 

THANKS, RON, You're a genius. There WAS a space at the end of the
categories. I deleted the extra space and my equation works like a charm!!!
THANK YOU FOR ALL YOUR HELP.
I must say, this is a perfect place to find answers and save a lot of
frustration.


"Ron Rosenfeld" wrote:

On Thu, 27 Sep 2007 17:48:00 -0700, Grams
wrote:

OK. So now I tried to sumproduct two other columns using the same
formula...and changing the column letters to match the new count I am looking
for - but it always returns a zero even though there is data there.
I want to find how many kids in each district have been evaluated for OI,
OHI, TBI, and Autism.
The District names (like "Camas") are in column E, rows 2 through 200
The Categories (OI, OHI, etc) are in column J, rows 2 through 200
I tried =SUMPRODUCT((E2:E200="Camas")*(J2:J200="TBI"))
Didn't work.
Any suggestions?
Thanks

One likely explanation is that your cells don't contain exactly what you think
they do.

It may be that either the district, or the disease, also contain either
<space's or some other non-printing character.

If cleaning up the table is not possible, then you could try this formula which
uses wildcards to search for the information. You would have to ensure that
using a wildcard won't detect contents you don't want.

=SUMPRODUCT(ISNUMBER(SEARCH("*camas*",E2:E200))*IS NUMBER(SEARCH("*tbi*",J2:J200)))
--ron

sum product not returning correct number
 

On Thu, 27 Sep 2007 22:28:00 -0700, Grams
wrote:

THANKS, RON, You're a genius. There WAS a space at the end of the
categories. I deleted the extra space and my equation works like a charm!!!
THANK YOU FOR ALL YOUR HELP.
I must say, this is a perfect place to find answers and save a lot of
frustration.


You're very welcome. Thanks for the feedback.

All of us have used this forum as a frustration reliever :-)
--ron