| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting if between date range
I have a column of dates such as 8/5/06, I would like to create a formula
that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting if between date range
Something like this might be what you're looking for.........
=COUNTIF(A:A,"=9/1/06")-COUNTIF(A:A,"=10/1/06") Vaya con Dios, Chuck, CABGx3 "Marc Shaw" wrote: I have a column of dates such as 8/5/06, I would like to create a formula that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting if between date range
Marc
one way: =SUMPRODUCT( --(G1:G7=DATE(2006,8,1)),-- (G1:G7<=DATE(2006,8,31)) ) =SUMPRODUCT( --(G1:G7=DATE(2006,9,1)),-- (G1:G7<=DATE(2006,9,30)) ) assuming the dates are in cells G1 to G7 Regards Trevor "Marc Shaw" wrote in message ... I have a column of dates such as 8/5/06, I would like to create a formula that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting if between date range
One way:
=SUMPRODUCT(--(MONTH(B1:B20)=8)) =SUMPRODUCT(--(MONTH(B1:B20)=9)) HTH "Marc Shaw" wrote: I have a column of dates such as 8/5/06, I would like to create a formula that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting if between date range
Here's one way to get all 12 months (assuming the year is the same):
Assuming your data is in the range A1:A7. Enter this formula for the month name in say, E1: =TEXT(DATE(2006,ROWS($1:1),1),"mmmm") Enter this formula for the count in F1: =SUMPRODUCT(--(MONTH(A$1:A$7)=ROWS($1:1))) Select both E1 and F1 then copy down to row 12. Biff "Marc Shaw" wrote in message ... I have a column of dates such as 8/5/06, I would like to create a formula that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting if between date range
Marc,
=SUMPRODUCT(--(MONTH(A1:A1000)=8)) =SUMPRODUCT(--(MONTH(A1:A1000)=9)) HTH, Bernie MS Excel MVP "Marc Shaw" wrote in message ... I have a column of dates such as 8/5/06, I would like to create a formula that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting if between date range
Thank you Chuck, I knew it should be an easy formula but the brain just
wasn't clicking. Thanks again! -- Thanks, Marc Shaw "CLR" wrote: Something like this might be what you're looking for......... =COUNTIF(A:A,"=9/1/06")-COUNTIF(A:A,"=10/1/06") Vaya con Dios, Chuck, CABGx3 "Marc Shaw" wrote: I have a column of dates such as 8/5/06, I would like to create a formula that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting if between date range
Happy to help, Marc...........thanks for the feedback.
Vaya con Dios, Chuck, CABGx3 "Marc Shaw" wrote: Thank you Chuck, I knew it should be an easy formula but the brain just wasn't clicking. Thanks again! -- Thanks, Marc Shaw "CLR" wrote: Something like this might be what you're looking for......... =COUNTIF(A:A,"=9/1/06")-COUNTIF(A:A,"=10/1/06") Vaya con Dios, Chuck, CABGx3 "Marc Shaw" wrote: I have a column of dates such as 8/5/06, I would like to create a formula that counts the number of dates for a particular month. For example, if the column reads as such: 9/13/06 8/28/06 9/20/06 9/20/06 8/20/06 9/22/06 9/22/06 I would like the formula to tell me that there are 2 for the month of August. Then I could also set up another similar formular to tell me that there are 5 for the month of September -- Thanks, Marc Shaw |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
| Return info based on Date Range | Excel Worksheet Functions | |||
| Return a specified date when it falls within a range.... | Excel Discussion (Misc queries) | |||
| Computing a date range | Excel Worksheet Functions | |||
Linear Mode
