From: Tmt on
Hello,

I've used this formular to find work piece that gets done per department.
=SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))

Department 14532 was not picked up even though other department's number was
correctly counted. But if I put a letter, say like C (14532C) and adds C to
my formular as in
=SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it picks
up how many pieces were done for this department.

I suspect this is in my cell format but I could not figure out what went
wrong. Please help. Thanks.

Tmt
From: Don Guillett on
try withOUT the " "

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Tmt" <Tmt(a)discussions.microsoft.com> wrote in message
news:2E3CDDFC-848F-47C8-9DFE-531CD6DE94AD(a)microsoft.com...
> Hello,
>
> I've used this formular to find work piece that gets done per department.
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))
>
> Department 14532 was not picked up even though other department's number
> was
> correctly counted. But if I put a letter, say like C (14532C) and adds C
> to
> my formular as in
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it picks
> up how many pieces were done for this department.
>
> I suspect this is in my cell format but I could not figure out what went
> wrong. Please help. Thanks.
>
> Tmt

From: Tmt on
Don,

Thanks for the suggestion. Dropping the " " for those problematic set of
data makes those items counted for.

But here's another problem. I also discovered that this same formula without
the " " would not read other set of data or miscounted some other set of data
(4 items done from Q2 data sheet only returned as 3 items done). How do I
unify all so that one formula works without keeping the " " and not for the
others?

Thanks.

Tmt

"Tmt" wrote:

> Hello,
>
> I've used this formular to find work piece that gets done per department.
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))
>
> Department 14532 was not picked up even though other department's number was
> correctly counted. But if I put a letter, say like C (14532C) and adds C to
> my formular as in
> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it picks
> up how many pieces were done for this department.
>
> I suspect this is in my cell format but I could not figure out what went
> wrong. Please help. Thanks.
>
> Tmt
From: Don Guillett on
Perhaps some "numbers" are text and some are numbers. Change all to numbers.
Sub fixmynums()
Application.ScreenUpdating = False
'lr = Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For Each c In Selection 'Range("a1:q" & lr)
If Trim(Len(c)) > 0 And c.HasFormula = False Then
c.NumberFormat = "General"
c.Value = CDbl(c)
End If
Next

Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Tmt" <Tmt(a)discussions.microsoft.com> wrote in message
news:4395DDCE-EF55-4571-A372-842BE8F55649(a)microsoft.com...
> Don,
>
> Thanks for the suggestion. Dropping the " " for those problematic set of
> data makes those items counted for.
>
> But here's another problem. I also discovered that this same formula
> without
> the " " would not read other set of data or miscounted some other set of
> data
> (4 items done from Q2 data sheet only returned as 3 items done). How do I
> unify all so that one formula works without keeping the " " and not for
> the
> others?
>
> Thanks.
>
> Tmt
>
> "Tmt" wrote:
>
>> Hello,
>>
>> I've used this formular to find work piece that gets done per department.
>> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532"))
>>
>> Department 14532 was not picked up even though other department's number
>> was
>> correctly counted. But if I put a letter, say like C (14532C) and adds C
>> to
>> my formular as in
>> =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it
>> picks
>> up how many pieces were done for this department.
>>
>> I suspect this is in my cell format but I could not figure out what went
>> wrong. Please help. Thanks.
>>
>> Tmt