SUMPRODUCT
in [General]
|
From: Tmt on 6 May 2010 17:35 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 6 May 2010 17:53 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 7 May 2010 13:07 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 8 May 2010 08:12 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
|
Pages: 1 Prev: Sumproduct Approach Next: Conditional Formatting - Test Practice |