SUMPRODUCT again!

Hello,

I need help solving this formula:
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521"))

where the numbers of "done" items is counted under department 14521. This
formula works with many departments but skipped department# 14521. But if I
put a letter, say like 14521S, in my Q2 datasheet and changed this formula to
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521S"))
then the formula worked again, counting every one that I added the S behind
those data.

I suspected the cell format and try to format the cells Catergory to
"General", but that didn't help.

What's wrong? Please help. Thanks.

Tmt

Remove the ""s from the number
example "14521" - should be 14521
--
Wag more, bark less


"Tmt" wrote:

Hello,

I need help solving this formula:
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521"))

where the numbers of "done" items is counted under department 14521. This
formula works with many departments but skipped department# 14521. But if I
put a letter, say like 14521S, in my Q2 datasheet and changed this formula to
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521S"))
then the formula worked again, counting every one that I added the S behind
those data.

I suspected the cell format and try to format the cells Catergory to
"General", but that didn't help.

What's wrong? Please help. Thanks.

Tmt

If I didn't put a letter behind my data, the sumproduct counted as zero,
nothing picked up.

Please help. Thanks.

"Tmt" wrote:

Hello,

I need help solving this formula:
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521"))

where the numbers of "done" items is counted under department 14521. This
formula works with many departments but skipped department# 14521. But if I
put a letter, say like 14521S, in my Q2 datasheet and changed this formula to
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521S"))
then the formula worked again, counting every one that I added the S behind
those data.

I suspected the cell format and try to format the cells Catergory to
"General", but that didn't help.

What's wrong? Please help. Thanks.

Tmt

On Fri, 7 May 2010 09:14:01 -0700, Tmt
wrote:

Hello,

I need help solving this formula:
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150 ="14521"))

where the numbers of "done" items is counted under department 14521. This
formula works with many departments but skipped department# 14521. But if I
put a letter, say like 14521S, in my Q2 datasheet and changed this formula to
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150 ="14521S"))
then the formula worked again, counting every one that I added the S behind
those data.

I suspected the cell format and try to format the cells Catergory to
"General", but that didn't help.

What's wrong? Please help. Thanks.

Tmt



Try this formula:

=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150& ""="14521"))

the &"" makes the left hand of the comparison a string like the right
hand.

Hope this helps / Lars-Åke

My best guess is that there is something there that you aren't seeing

Assume that the "number" in question is H5
Assume that I5 is blank
type in =H5=14521
You should get false
type in =H5="14521"
hopefully you will get false
type in =H5="14521 " - having a trailing space

Do you get True?

Is so change your sumproduct to "14521 " (with the trail space) rather than
"14521"

Success?- if yes then click the "yes" button saying this post was helpful...

--
Wag more, bark less


"Tmt" wrote:

If I didn't put a letter behind my data, the sumproduct counted as zero,
nothing picked up.

Please help. Thanks.

"Tmt" wrote:

Hello,

I need help solving this formula:
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521"))

where the numbers of "done" items is counted under department 14521. This
formula works with many departments but skipped department# 14521. But if I
put a letter, say like 14521S, in my Q2 datasheet and changed this formula to
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521S"))
then the formula worked again, counting every one that I added the S behind
those data.

I suspected the cell format and try to format the cells Catergory to
"General", but that didn't help.

What's wrong? Please help. Thanks.

Tmt

Brad,

This brings up more question. This is what I get:
=G125="14521" -------- FALSE
=G125=" 14521" ------- FALSE
=G125="14521 " -------- FALSE
=G125=14521 ---------- TRUE

While another cell will behaves differently:
=G111="10223" ---------TRUE
=G111=" 10223" --------FALSE
=G111="10223 " --------FALSE
=G111=10223 -----------FALSE

I'm confused! Can all cells be formatted to accept one set of rule instead
of tinkering with " " for this cell but not the next?

Tmt

"Tmt" wrote:

Hello,

I need help solving this formula:
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521"))

where the numbers of "done" items is counted under department 14521. This
formula works with many departments but skipped department# 14521. But if I
put a letter, say like 14521S, in my Q2 datasheet and changed this formula to
=SUMPRODUCT(('Q2'!E13:E150="done")*('Q2'!G13:G150= "14521S"))
then the formula worked again, counting every one that I added the S behind
those data.

I suspected the cell format and try to format the cells Catergory to
"General", but that didn't help.

What's wrong? Please help. Thanks.

Tmt