From: Tmt on
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
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
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
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
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
> >
> >
 |  Next  |  Last
Pages: 1 2
Prev: validation
Next: email as pdf print area excel 2007