I have a complex array formula where I'm trying to add one condition, and I
can't get it to work- so after way too much frustration, I'm here to ask you
experts for some help/syntax. I need to be able to tell (for a given target
date) how many incidents were open as of that date (which is working) and
now, how old those incidents were- in this case, how many were less than 10
days old (not working);

In my source worksheet (Total04) I have the following information:
-------------------------------------------------------------------
Column B: many ID's, including one that I'm looking for
Column E: "Start date" which has a start date or may be
blank (I ignore all records that have no start date)
Column F: "End date" which may have an end date, or may
be blank (which indicates that it hasn't been closed
yet). I ignore entries where the end date preceeds
my target date, because those entries were already
closed by that date.

In my active worksheet ('Rept Age') I reference two cells; L2 has the ID I'm
matching, and B4 has the target date I'm using.

In my _working_ formula I am just looking to see how many events occurred
which started before my specific date and were still open as of that date.
This formula appears to be working;
=SUM((IF(Total04!B$6:B2000='REPT
Age'!L$2,1,0))*(IF(Total04!E$6:E20000,1,0))*(IF(T otal04!F$6:F2000="",1,0)+I
F(Total04!F$6:F2000=B4,1,0))*(IF(Total04!E$6:E200 0<=B4,1,0)))

'First IF: does it match my target ID
'Second IF: is the start date 0 (e.g., does it have a start date)
'Third & Fourth IF: Either there is no end date, or, the end date is later
than the target date
'Fifth IF: Verify that start date is actually before my target date

Now I need to break it down into buckets of how old each event was as of my
target date- for example, 1-10 days, 11-20 days, etc.

So, I thought I'd add one more condition. Here is the same formula, where I
have added my one additional condition, but all I get is the #Value and I
haven't figured out why. Any help would be greatly appreciated!

=SUM( (IF(Total04!B$6:B2000='UIR Age'!L$2,1,0))
*(IF(Total04!E$6:E20000,1,0))
*(IF(Total04!F$6:F2000="",1,0)+IF(Total04!F$6:F200 0=B4,1,0))
*(IF(Total04!E$6:E2000<=B4,1,0))*IF(Total04!E$6:E2 000+10=B4,1,0))

So all I added was the condition (multiplied in) of:
IF(Total04!E$6:E2000+10=B4,1,0)
because if the start date +10 [days] is greater than my target date, then I
know that the event was less than 10 days old as of my target date
(regardless of when or whether the event was ever closed).

All I get is "#Value". Does it have something to do with the +10 not being
in an array? If so, what is the most eloquent way to fix it?

help!?!
Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Somehow posting is magical- if I find the answer myself at all, it is always
right after I post :-/

Instead of trying to affect the array value, I just pushed the value to the
other side of the equation:

IF(Total04!E$6:E2000+10=B4,1,0)

became

IF(Total04!E$6:E2000=B4-10,1,0)

and of course now it works perfectly [sigh]

Sorry for the bandwidth on the original post!