Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting dates in a column
I know this might have been worded differently in another question, but I
can't make anything work that I have tried so far. I have a column of dates in mm/dd/yy format on one sheet, they are all different (graduation dates of students). 05/12/05 06/05/03 12/12/56 06/06/03 ect. In a cell on different sheet, I need to count how many students graduated in a certain month and year. Result is Number of students graduated in Jun 03? I can count the month, or I can count the year, but I can't return both the month and year because of the day in the middle! Thanks in advance for taking a look at this for me. Rob |
#2
|
|||
|
|||
Try...
=SUMPRODUCT(--(MONTH(A1:A10)=6),--(YEAR(A1:A10)=2003)) OR =SUMPRODUCT(--(DATE(YEAR(A1:A10),MONTH(A1:A10),1)=DATE(2003,6,1) )) Hope this helps! In article , Robb27 wrote: I know this might have been worded differently in another question, but I can't make anything work that I have tried so far. I have a column of dates in mm/dd/yy format on one sheet, they are all different (graduation dates of students). 05/12/05 06/05/03 12/12/56 06/06/03 ect. In a cell on different sheet, I need to count how many students graduated in a certain month and year. Result is Number of students graduated in Jun 03? I can count the month, or I can count the year, but I can't return both the month and year because of the day in the middle! Thanks in advance for taking a look at this for me. Rob |
#3
|
|||
|
|||
Hi!
Graduation dates are on Sheet1 A1:A100 Sheet2: A1 = 6 (for the month - June) B1 = 2003 (year) =SUMPRODUCT(--(MONTH(Sheet1!A1:A100)=A1),--(YEAR(Sheet1!A1:A100)=B1)) Biff "Robb27" wrote in message ... I know this might have been worded differently in another question, but I can't make anything work that I have tried so far. I have a column of dates in mm/dd/yy format on one sheet, they are all different (graduation dates of students). 05/12/05 06/05/03 12/12/56 06/06/03 ect. In a cell on different sheet, I need to count how many students graduated in a certain month and year. Result is Number of students graduated in Jun 03? I can count the month, or I can count the year, but I can't return both the month and year because of the day in the middle! Thanks in advance for taking a look at this for me. Rob |
#4
|
|||
|
|||
On Tue, 24 May 2005 18:47:01 -0700, Robb27
wrote: I know this might have been worded differently in another question, but I can't make anything work that I have tried so far. I have a column of dates in mm/dd/yy format on one sheet, they are all different (graduation dates of students). 05/12/05 06/05/03 12/12/56 06/06/03 ect. In a cell on different sheet, I need to count how many students graduated in a certain month and year. Result is Number of students graduated in Jun 03? I can count the month, or I can count the year, but I can't return both the month and year because of the day in the middle! Thanks in advance for taking a look at this for me. Rob If your dates are in a range labeled Dates on Sheet1, and are normal Excel dates, then: =COUNTIF(Dates,"="&DATE(2003,6,1)) - COUNTIF(Dates,""&DATE(2003,6,30)) --ron |
#5
|
|||
|
|||
"Domenic" wrote in message ... Try... =SUMPRODUCT(--(MONTH(A1:A10)=6),--(YEAR(A1:A10)=2003)) or =SUMPRODUCT(--(TEXT(A1:A6,"mmyyyy")="062003")) just an alternative <g |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
up to 7 functions? | Excel Worksheet Functions | |||
Counting Dates | Excel Worksheet Functions | |||
Counting NUMBERS &/or TEXT from a column to other sheet ? | Excel Worksheet Functions | |||
Counting a Running Column | Excel Worksheet Functions |