SUMPRODUCT

[Tmt]

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

 

[Don Guillett]

try withOUT the " "

 

[Tmt]

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

 

[Don Guillett]

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