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

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

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

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

"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