|
SUMPRODUCT
I have a sumproduct
=SUMPRODUCT(--(ProblemClosedDate=$D24),--(ActionClosedDate<" "),--(LOB=$E24)) which is filtering down on the 3 defined names, all having the same similiar reference, i.e. =OFFSET('RCA Tracker'!$O$1,0,0,COUNTA('RCA Tracker'!$AG:$AG),1) It used to work fine however, I have changed column O which is the ProblemClosedDate not to be a manually inputted date but auto populated, hence it is now =IF(AC812=1," ",IF(AE812,AE812," ")) If the sumproduct was =D24 then it would work but since I need it as = it returns far to many. Do I need to use INdirect and if so should it be in the defined name or the sumproduct, either way have you the text since i don't know the syntax? thx |
SUMPRODUCT
One possibility...
This formula can return a space character... =IF(AC812=1," ",IF(AE812,AE812," ")) This test will evaluate to 1 (TRUE) when it encounters the space characters from the above formula. --(ProblemClosedDate=$D24) A TEXT value *always* evalautes to be greater than *any numeric* value: =" "1E100 = TRUE Use another array and test that ProblemClosedDate is a number: --(ISNUMBER(ProblemClosedDate)),--(ProblemClosedDate=$D24) -- Biff Microsoft Excel MVP "Paul Dennis" wrote in message ... I have a sumproduct =SUMPRODUCT(--(ProblemClosedDate=$D24),--(ActionClosedDate<" "),--(LOB=$E24)) which is filtering down on the 3 defined names, all having the same similiar reference, i.e. =OFFSET('RCA Tracker'!$O$1,0,0,COUNTA('RCA Tracker'!$AG:$AG),1) It used to work fine however, I have changed column O which is the ProblemClosedDate not to be a manually inputted date but auto populated, hence it is now =IF(AC812=1," ",IF(AE812,AE812," ")) If the sumproduct was =D24 then it would work but since I need it as = it returns far to many. Do I need to use INdirect and if so should it be in the defined name or the sumproduct, either way have you the text since i don't know the syntax? thx |
SUMPRODUCT
ProblemClosedDate=$D24 doesn't work but ProblemClosedDate=$D24 does as using
= checks that's is an exact match but using = finds the date but everything greater which seems to also include formula's. "Sandy Mann" wrote: It looks like you are using spaces to mean empty cells so --(ActionClosedDate<" "), will return 1 for every empty cell. Could this be whxt is causing you to have too many returns. If not what exactly do you mean by: If the sumproduct was =D24 then it would work but since I need it as = it returns far to many. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Paul Dennis" wrote in message ... I have a sumproduct =SUMPRODUCT(--(ProblemClosedDate=$D24),--(ActionClosedDate<" "),--(LOB=$E24)) which is filtering down on the 3 defined names, all having the same similiar reference, i.e. =OFFSET('RCA Tracker'!$O$1,0,0,COUNTA('RCA Tracker'!$AG:$AG),1) It used to work fine however, I have changed column O which is the ProblemClosedDate not to be a manually inputted date but auto populated, hence it is now =IF(AC812=1," ",IF(AE812,AE812," ")) If the sumproduct was =D24 then it would work but since I need it as = it returns far to many. Do I need to use INdirect and if so should it be in the defined name or the sumproduct, either way have you the text since i don't know the syntax? thx |
SUMPRODUCT
"Paul Dennis" wrote in message
... ProblemClosedDate=$D24 doesn't work but ProblemClosedDate=$D24 does as using = checks that's is an exact match but using = finds the date but everything greater which seems to also include formula's. If you mean that you have dates and other numbers returned by formulas then I think that you are stuck. CELL() can tell you if a cell contains a date but it will not work on a range. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Paul Dennis" wrote in message ... ProblemClosedDate=$D24 doesn't work but ProblemClosedDate=$D24 does as using = checks that's is an exact match but using = finds the date but everything greater which seems to also include formula's. "Sandy Mann" wrote: It looks like you are using spaces to mean empty cells so --(ActionClosedDate<" "), will return 1 for every empty cell. Could this be whxt is causing you to have too many returns. If not what exactly do you mean by: If the sumproduct was =D24 then it would work but since I need it as = it returns far to many. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Paul Dennis" wrote in message ... I have a sumproduct =SUMPRODUCT(--(ProblemClosedDate=$D24),--(ActionClosedDate<" "),--(LOB=$E24)) which is filtering down on the 3 defined names, all having the same similiar reference, i.e. =OFFSET('RCA Tracker'!$O$1,0,0,COUNTA('RCA Tracker'!$AG:$AG),1) It used to work fine however, I have changed column O which is the ProblemClosedDate not to be a manually inputted date but auto populated, hence it is now =IF(AC812=1," ",IF(AE812,AE812," ")) If the sumproduct was =D24 then it would work but since I need it as = it returns far to many. Do I need to use INdirect and if so should it be in the defined name or the sumproduct, either way have you the text since i don't know the syntax? thx |
| All times are GMT +1. The time now is 04:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com