Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurance of a month in a column of dates? | Excel Worksheet Functions | |||
Count number of cells of a particular month in a column of dates | Excel Discussion (Misc queries) | |||
Count if date is between two dates and value in another column equ | Excel Worksheet Functions | |||
count no. of dates in a column that falls on certain month & year | Excel Worksheet Functions | |||
Count cells in a column that contain dates | New Users to Excel |