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
|