Tricky counting question

[Jay Weiss]

Hi,

I have a spreadsheet in which each row represents a different process.
I have three columns that represent the process start date, the process
end date, and the process end time. What I'd like to do is come up
with a count for each row of how many *other* processes were between
their start date and end date/time when each process ended.

Here's an example:
Row 1: StartDate 04/01/06 EndDate 04/05/06 EndTime: 20:00:00
Row 2: StartDate 04/02/06 EndDate 04/10/06 EndTime: 07:00:00
Row 3: StartDate 04/02/06 EndDate 04/04/06 EndTime: 10:00:00
Row 4: StartDate 04/06/06 EndDate 04/10/06 EndTime: 05:00:00

When the process in Row 1 ends, the process in Row 2 is active, so the
count for Row 1 is 1.
When the process in Row 2 ends, none of the other processes is active
(Row 4 ended two hours earlier), so the count for Row 2 is 0.
When the process in Row 3 ends, the processes in Rows 1 and 2 are
active, so the count for Row 3 is 2.
When the process in Row 4 ends, the process in Row 2 is active, so the
count for Row 4 is 1.

Assuming StartDate is Column A, EndDate is Column B, and EndTime is
Column C, how do I calculate the count and put it in Column D?

Thanks to anyone who is so gracious as to help out with this tricky
question...

....Jay

 

[Bob Phillips]

=SUMPRODUCT(--($A$1:$A$4<=B1+C1),--($B$1:$B$4+$C$1:$C$4>=B1+C1))-1

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jay Weiss]

Hi Bob,

It works like a charm! Thanks!

....Jay