SUMPRODUCT again!

[Tmt]

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]

Remove the ""s from the number
example "14521" - should be 14521

 

[Tmt]

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

Please help. Thanks.

 

[Lars-Åke Aspelin]

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

 

[Brad]

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

 

[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