Assume...
A B C
1 Event Start Date End Date
2 Circus 01/01/2006 01/05/2006
3 Play 01/01/2006 01/03/2006
4 Auction 01/02/2006 01/04/2006
5 Debate 01/03/2006 01/03/2006
6 Game 01/04/2006 01/05/2006
In E1, put "Dates"
In F1, put "Events"
In E2 down to E???, put the dates you want to check on.
In F2 put the following ARRAY formula. That means, when you have completed
the formula, instead of hitting ENTER, you hit CTRL-SHIFT-ENTER. The { and }
will appear at the beginning and end of the formula. Copy this formula down
to the end of your 'Dates' list.
Formula in F2:
=SUM(IF($B$2:$B$6<=E2,1,0)*IF($C$2:$C$6>=E2,1,0))
after hitting Ctrl-Shift-Enter will look like...
{=SUM(IF($B$2:$B$6<=E2,1,0)*IF($C$2:$C$6>=E2,1,0))}
Your worksheet should now look something like...
A B C D E F
1 Event Start Date End Date Dates Events
2 Circus 01/01/2006 01/05/2006 01/01/2006 2
3 Play 01/01/2006 01/03/2006 01/02/2006 3
4 Auction 01/02/2006 01/04/2006 01/03/2006 4
5 Debate 01/03/2006 01/03/2006 01/04/2006 3
6 Game 01/04/2006 01/05/2006 01/05/2006 2
7 01/06/2006 0
8 01/07/2006 0
9 01/08/2006 0
HTH,
