Formula needed for 2 criteria
 

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

Formula needed for 2 criteria
 

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

Formula needed for 2 criteria
 

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

Formula needed for 2 criteria
 

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