If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
SUMPRODUCT again!
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 |
#3
|
|||
|
|||
SUMPRODUCT again!
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 |
#4
|
|||
|
|||
SUMPRODUCT again!
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 |
#5
|
|||
|
|||
SUMPRODUCT again!
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 |
#6
|
|||
|
|||
SUMPRODUCT again!
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 |
Thread Tools | |
Display Modes | |
|
|