SUMPRODUCT vs. COUNTIF -- Why does one work?

J

javamom

I'm stumped on this and reading through the archived messages has not
shed any light yet.

Using =COUNTIF(V2:V11,"*Very Clear*") to determine the number of times
"Very Clear" is listed works great.

Using
=SUMPRODUCT(--(E2:E11=DATEVALUE("10/19/2004")),--(V2:V11="*Very*Clear*"))
to determine the number times "Very Clear" is listed on a particular
date returns the inaccurate value of "0".

What am I missing? Thanks! Trish (Excel 2000, WinXP)
 
P

Peo Sjoblom

One way

=SUMPRODUCT(--(E2:E11=--"2004-10-19"),--(ISNUMBER(SEARCH("Very
Clear",V2:V11))))


note that I removed datevalue, it is of no real value, I also changed the
date format to a date that will translate to other regional date systems

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
J

javamom

I tried your suggestion and it still returns a 0.

I noticed you removed the asterisks around the "Very Clear" but I've
found I need to have those to make the COUNTIF work. Could the problem
be that the text itself is not very clean and extra characters are
causing a problem with SUMPRODUCT?

Oddly, if I copy the contents from one of the text cells and paste it
into the Excel find/replace feature, the program cannot find that text.
I'm starting to suspect that I have weird text characters because this
content has traveled across many platforms before being imported into
the spreadsheet. Is SUMPRODUCT sensitive in maaner that COUNTIF is not?


Grasping at straws... Trish
 
B

Bob Phillips

javamom said:
I tried your suggestion and it still returns a 0.

I noticed you removed the asterisks around the "Very Clear" but I've
found I need to have those to make the COUNTIF work. Could the problem
be that the text itself is not very clean and extra characters are
causing a problem with SUMPRODUCT?

Oddly, if I copy the contents from one of the text cells and paste it
into the Excel find/replace feature, the program cannot find that text.
I'm starting to suspect that I have weird text characters because this
content has traveled across many platforms before being imported into
the spreadsheet. Is SUMPRODUCT sensitive in maaner that COUNTIF is not?


Grasping at straws... Trish
Click to expand...

Peo removed the atserisk because he did a SEARCH in its place, which
caters for the string within.

Maybe the wrap-around did you, try

=SUMPRODUCT(--(E2:E11=--"2004-10-19"),
--(ISNUMBER(SEARCH("Very Clear",V2:V11))))
 
Top