I am trying to put our Kindergarten rolls onto the computer. Rows 6-46 have
the details of the children admitted onto our rolls. Column I has the start
dates and column K has the finish dates. In row 4 from column M to column BO
are the dates that the Kindergarten is open for each full term. The dates in
columns I and K range randomly from 5/10/04 to 7/02/06... (The date range
will continue to alter as more children start and more children leave. The
dates the Kindergarten will be open will change from year to year also, so I
do not want a formula that refers specifically to the date, it needs to refer
to the cell as the date for the M4 cell for eg. will be different next year.)
e.g. I would like a formula that will identify the number of cells that have
a start date (Column I) less than Cell M4 (e.g. 7/02/06) and then of those
identified cells, how many also have a finish date greater than cell M4
(7/02/06). I want to know how many children are on the rolls for each day
that the Kindergarten is open.
Could someone please help?
Sue

This will count how many dates are previous to M4 in column I

=COUNTIF(I4:I500,"<"&M4)

I am assuming you don't have more than 496 kids LOL


this will count how many are previous to M4 AND have end dates in K that are
later than M4

=SUMPRODUCT(--(I4:I500<M4),--(K4:K500M4))

you might want to include M4 as well since these formulas are not checking
equal to M4

=SUMPRODUCT(--(I4:I500<M4),--(K4:K500=M4))

or

=COUNTIF(I4:I500,"<="&M4)

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Sue" wrote in message
...
I am trying to put our Kindergarten rolls onto the computer. Rows 6-46
have
the details of the children admitted onto our rolls. Column I has the
start
dates and column K has the finish dates. In row 4 from column M to column
BO
are the dates that the Kindergarten is open for each full term. The dates
in
columns I and K range randomly from 5/10/04 to 7/02/06... (The date
range
will continue to alter as more children start and more children leave.
The
dates the Kindergarten will be open will change from year to year also,
so I
do not want a formula that refers specifically to the date, it needs to
refer
to the cell as the date for the M4 cell for eg. will be different next
year.)
e.g. I would like a formula that will identify the number of cells that
have
a start date (Column I) less than Cell M4 (e.g. 7/02/06) and then of those
identified cells, how many also have a finish date greater than cell M4
(7/02/06). I want to know how many children are on the rolls for each day
that the Kindergarten is open.
Could someone please help?
Sue

Hi Peo Sjoblom,
I have already tried both the countif and sumproduct formulas and found they
did not give me the results I needed. My example is that I have 35 children
on the roll based on their start dates as at 7/02/06 and the formula agrees
with that figure. However, once I enter in a finish date of 3/02/06 against
one of those children, then the figure goes up to 36??? It should not, it
should go down to 34 children currently on the rolls as at 7/02/06.
Can you please help me. I am really stuck on this one.

Regards
Sue

"Peo Sjoblom" wrote:

This will count how many dates are previous to M4 in column I

=COUNTIF(I4:I500,"<"&M4)

I am assuming you don't have more than 496 kids LOL


this will count how many are previous to M4 AND have end dates in K that are
later than M4

=SUMPRODUCT(--(I4:I500<M4),--(K4:K500M4))

you might want to include M4 as well since these formulas are not checking
equal to M4

=SUMPRODUCT(--(I4:I500<M4),--(K4:K500=M4))

or

=COUNTIF(I4:I500,"<="&M4)

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Sue" wrote in message
...
I am trying to put our Kindergarten rolls onto the computer. Rows 6-46
have
the details of the children admitted onto our rolls. Column I has the
start
dates and column K has the finish dates. In row 4 from column M to column
BO
are the dates that the Kindergarten is open for each full term. The dates
in
columns I and K range randomly from 5/10/04 to 7/02/06... (The date
range
will continue to alter as more children start and more children leave.
The
dates the Kindergarten will be open will change from year to year also,
so I
do not want a formula that refers specifically to the date, it needs to
refer
to the cell as the date for the M4 cell for eg. will be different next
year.)
e.g. I would like a formula that will identify the number of cells that
have
a start date (Column I) less than Cell M4 (e.g. 7/02/06) and then of those
identified cells, how many also have a finish date greater than cell M4
(7/02/06). I want to know how many children are on the rolls for each day
that the Kindergarten is open.
Could someone please help?
Sue

Without seeing your data I am not sure. But if people have a blank
leaving date until it is know they are leaving, entering a leaving date
would increase the figure! as the second criteria would not be met. So
entering a finish date could cause an increase

Try
=SUMPRODUCT(($I$4:$I$500<=M4)*OR(ISBLANK($i$4:$i$5 00),(
$K$4:$K$500=M4)))

This allows for blank finish dates

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=529891