count dates in a column
 

Hi,

This seems a simple problem, but I'm really an Excel novice. I have a list
of animal IDs in Column A with their pregnancy due dates in Column H. I need
to know how many animals are due within a specific date range, for example:
from 1 Jan 08 up to and including 17 Jan 08.

Column A Column H
219 1 Jan 08
222 1 Jan 08
334 14 Jan 08
267 17 Jan 08
144 21 Jan 08
008 21 Jan 08
456 27 Jan 08
298 2 Feb 08 ................ and so on.

count dates in a column
 

One way

Source data assumed in cols A and B, from row2 down
where col B = real dates

Assuming in D2: start date, in E2: end date
you could put in F2:
=SUMPRODUCT((B2:B100=D2)*(B2:B100<=E2))
to return the count of dates in col B
which fall between the start-end dates input in D2:F2.

Adapt the range to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"CJP" wrote:
Hi,

This seems a simple problem, but I'm really an Excel novice. I have a list
of animal IDs in Column A with their pregnancy due dates in Column H. I need
to know how many animals are due within a specific date range, for example:
from 1 Jan 08 up to and including 17 Jan 08.

Column A Column H
219 1 Jan 08
222 1 Jan 08
334 14 Jan 08
267 17 Jan 08
144 21 Jan 08
008 21 Jan 08
456 27 Jan 08
298 2 Feb 08 ................ and so on.

count dates in a column
 

It's probably better to put the dates into two cells so that you can
easily change them without affecting the formula, so use M1 for the
earlier date and M2 for the later date. Then in N1 put this formula:

=SUMPRODUCT((H1:H100=M1)*(H1:H100<=M2))

I've assumed data occupies up to 100 rows, but change the ranges if
necessary.

Hope this helps.

Pete

On Nov 17, 12:32 am, CJP wrote:
Hi,

This seems a simple problem, but I'm really an Excel novice. I have a list
of animal IDs in Column A with their pregnancy due dates in Column H. I need
to know how many animals are due within a specific date range, for example:
from 1 Jan 08 up to and including 17 Jan 08.

Column A Column H
219 1 Jan 08
222 1 Jan 08
334 14 Jan 08
267 17 Jan 08
144 21 Jan 08
008 21 Jan 08
456 27 Jan 08
298 2 Feb 08 ................ and so on.