Count cells between a date range

[BradKopecky]

I need a forumula that helps me show the number of students enrolled in
a class on any given day.

I have a spreadsheet with two worksheets. One tab has a list of
students and their start and end date in the class, the other tab has a
list of dates in column A.

In column B of the tab with dates, I am looking for a count of how many
students have a start date >= AND <= the end date for that particular
day.

I have tried a Conditional Counta, but something is not working. Here
is what I have for a forumla that I am trying to make work. In that
formula A4 is a date in column A. For some reason, the formula returns
9,999 rather than an accurate count.

{=COUNTA(IF(Clients!$C$2:$C$10000>=A4,IF(Clients!$D$2:$D$10000<=A4,Clients!$A$2:$A$10000,0),0))}

 

[daddylonglegs]

I take it column C contains start dates and column D end dates?

Try

=COUNTIF(Clients!$C$2:$C$10000,"<="&A4)-COUNTIF(Clients!$D$2:$D$10000,"<"&A4)

 

[BradKopecky]

That solution did not work. Here is some sample data from the second
tab. Ignore the value column, it was there from an attempt to use a
Conditional Sum formula.

Data from Clients Tab
Client Name Value Start Date End Date
Magilla Gorilla 1 1/1/06 3/1/06
George Jetson 1 12/1/05 1/5/06
Jane Jetson 1 1/1/06 2/20/06
Bart Simpson 1 1/10/06 3/15/06
Homer Simpson 1 1/20/06 2/1/06
Marge Simpson 1 1/1/06 2/15/06
Maggie Simpson 1 1/15/06 3/1/06
Lisa Simpson 1 1/20/06 3/1/06

Here are the rows from the Date Check tab
1/4/2006
1/5/2006
1/6/2006
1/7/2006
1/8/2006
1/9/2006

I am looking for a formula to tell me how many students are in the
program on each day.