Counting number of observations within a date range?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have two columns with dates in my dataset, which define
the start and end dates for a certain event. For each
observation in the dataset, I'd like to count the
number of other observations in the dataset that
overlap with this event window.

I.e.

Event Start date End date Simultaneous event
-----------------------------------------------------------------------------------
XYZ 11jan04 07feb04 ???
ZZZ 11jan04 08feb04 ???
YYY 14jan04 22jan04 ???
XXX 21jan04 13mar04 ???
ZZY 30jan04 04feb04 ???

aso.aso.


Many thanks for your help!
 
If I understand your requirement:

in D3:

(I have assumed data starts in row 2 so change 2 to whatever in formula
below):

=SUMPRODUCT(--($B$2:$B$7>=B2),--($C$2:$C$7<=C2))

Copy down as required

HTH
 
...... I am wrong .. need to re-think!

Toppers said:
If I understand your requirement:

in D3:

(I have assumed data starts in row 2 so change 2 to whatever in formula
below):

=SUMPRODUCT(--($B$2:$B$7>=B2),--($C$2:$C$7<=C2))

Copy down as required

HTH
 
Yes, understood correctly. To specify again, the columns and rows are as
follows:

A B C D
----------------------------------------------
1 XYZ 11jan04 07feb04 ???
2 ZZZ 11jan04 08feb04 ???
3 YYY 14jan04 22jan04 ???
4 XXX 21jan04 13mar04 ???
5 ZZY 30jan04 04feb04 ???
and so on.
 
Nic,

With your starting dates in column B, starting in row 2, and ending dates in column C

=SUMPRODUCT((((B2<=$C$2:$C$1000)*(C2>=$C$2:$C$1000)+(C2>=$B$2:$B$1000)*(C2<=$C$2:$C$1000))>0)*1)-1

HTH,
Bernie
MS Excel MVP
 
Nic,

Two things: the formula cannot produce negative numbers, and you need to take off the -1: I was
counting the number of OTHER programs open, not including the project on the current line (my bad).
Anyway, if you post your email here or contact me privately, I will send you a working example
workbook.

HTH,
Bernie
MS Excel MVP
 
Sorry, by bad! I must have inserted the original formula you gave wrong into
my sheet. It's working now, thanks a lot you your help!

- Nic
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top