| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT formula help
Hi there,
I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT formula help
One way (for March 2007) is
=COUNTIF(A1:A400,"="&DATE(2007,3,1))-COUNTIF(A1:A400,"="&DATE(2007,4,1)) -- David Biddulph "GeorgeG" wrote in message ... Hi there, I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT formula help
Here is another way (for March 2007)....
=SUMPRODUCT(--(DATE(2007,3,DAY(A1:A400))=A1:A400)) Rick "GeorgeG" wrote in message ... Hi there, I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT formula help
=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or =SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07")) "GeorgeG" wrote: Hi there, I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT formula help
David, Rick, Smiley,
My problem is similar. I need to calculate the count of entries that lie-- 1. btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn 3 mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater than 9 mnths ago. All entries have a date associated with entry in colmn C. Thanks ahead of tme. "Teethless mama" wrote: =SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007)) or =SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07")) "GeorgeG" wrote: Hi there, I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT formula help
Define "a month ago". For example, what **date** would you consider a month
ago from today? Also, what **date** would you consider a month ago if today's date were October 31, 2007? Rick "Ken" wrote in message ... David, Rick, Smiley, My problem is similar. I need to calculate the count of entries that lie-- 1. btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn 3 mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater than 9 mnths ago. All entries have a date associated with entry in colmn C. Thanks ahead of tme. "Teethless mama" wrote: =SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007)) or =SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07")) "GeorgeG" wrote: Hi there, I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT formula help
A mnth ago would fall btwn today and 30 days prior today.
"Rick Rothstein (MVP - VB)" wrote: Define "a month ago". For example, what **date** would you consider a month ago from today? Also, what **date** would you consider a month ago if today's date were October 31, 2007? Rick "Ken" wrote in message ... David, Rick, Smiley, My problem is similar. I need to calculate the count of entries that lie-- 1. btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn 3 mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater than 9 mnths ago. All entries have a date associated with entry in colmn C. Thanks ahead of tme. "Teethless mama" wrote: =SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007)) or =SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07")) "GeorgeG" wrote: Hi there, I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT formula help
Okay, just to be clear then, a month ago from March 1, 2007 would be January
30, 2007 (skipping February entirely), correct? Are you using the same 30-day month for the other spans (3 months ago would be 90 days prior to today; 6 month ago would be 180 days prior to today, etc.)? Rick "Ken" wrote in message ... A mnth ago would fall btwn today and 30 days prior today. "Rick Rothstein (MVP - VB)" wrote: Define "a month ago". For example, what **date** would you consider a month ago from today? Also, what **date** would you consider a month ago if today's date were October 31, 2007? Rick "Ken" wrote in message ... David, Rick, Smiley, My problem is similar. I need to calculate the count of entries that lie-- 1. btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn 3 mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater than 9 mnths ago. All entries have a date associated with entry in colmn C. Thanks ahead of tme. "Teethless mama" wrote: =SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007)) or =SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07")) "GeorgeG" wrote: Hi there, I'm trying to create a formula that counts the number of times an event occurs for any given month, each year. For exmaple, i'd like to count the number of times an event occurs in March 2007, March 2006, etc. The dates stored on my spreadsheet are in the form of dd/mm/yyyy. Any help would be greatly appreciated! Cheers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
| Count Formula | Excel Discussion (Misc queries) | |||
| Trying to construct a count count formula | Excel Discussion (Misc queries) | |||
| Count formula Help... | Excel Discussion (Misc queries) | |||
| Count If formula | Excel Discussion (Misc queries) | |||
Hybrid Mode
