SUMPRODUCT again!

T

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
 
T

Tmt

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

Please help. Thanks.
 
L

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
 
B

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top