SUMPRODUCT again!
in [General]
|
Prev: validation
Next: email as pdf print area excel 2007
From: Tmt on 7 May 2010 12:14 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
From: Brad on 7 May 2010 12:20 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 > >
From: Tmt on 7 May 2010 12:46 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 > >
From: Lars-�ke Aspelin on 7 May 2010 14:05 On Fri, 7 May 2010 09:14:01 -0700, Tmt <Tmt(a)discussions.microsoft.com> 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
From: Brad on 7 May 2010 14:09
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 > > > > |