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

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:K500>M4))

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" <[email protected]> wrote in message
news:[email protected]...
>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:K500>M4))
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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$500),( $K$4:$K$500>=M4)))

This allows for blank finish dates

Regards

Dav