| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
countif number of occurences per month per year.
I am trying to calculate the count of items between months and years.
1/1/04 12/16/04 1/1/05 12/31/04 11/16/02 I need to say the following on another sheet Month Count November 02 1 January 04 1 December 04 2 January 05 1 Is there a way to do this? Thank you,PETE |
|
#2
|
|||
|
|||
|
One way, assume the range of dates are in A2:A50, for November 2002 it would be
=SUMPRODUCT(--(YEAR(A2:A50)=2002),--(MONTH(A2:A50)=11)) or preferably =SUMPRODUCT(--(YEAR(A2:A50)=C1),--(MONTH(A2:A50)=D1)) where you would put 2002 in C1 and 11 in D1, that way you can just change the year and month in those cells without having to edit the formula itself Regards, Peo Sjoblom "Pete Petersen" wrote: I am trying to calculate the count of items between months and years. 1/1/04 12/16/04 1/1/05 12/31/04 11/16/02 I need to say the following on another sheet Month Count November 02 1 January 04 1 December 04 2 January 05 1 Is there a way to do this? Thank you,PETE |
|
#3
|
|||
|
|||
|
You rock!....thank you so much for the quick response...you are amazing
"Peo Sjoblom" wrote: One way, assume the range of dates are in A2:A50, for November 2002 it would be =SUMPRODUCT(--(YEAR(A2:A50)=2002),--(MONTH(A2:A50)=11)) or preferably =SUMPRODUCT(--(YEAR(A2:A50)=C1),--(MONTH(A2:A50)=D1)) where you would put 2002 in C1 and 11 in D1, that way you can just change the year and month in those cells without having to edit the formula itself Regards, Peo Sjoblom "Pete Petersen" wrote: I am trying to calculate the count of items between months and years. 1/1/04 12/16/04 1/1/05 12/31/04 11/16/02 I need to say the following on another sheet Month Count November 02 1 January 04 1 December 04 2 January 05 1 Is there a way to do this? Thank you,PETE |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to extract month number from month name | Excel Discussion (Misc queries) | |||
| How do I convert a serial number to the month, day, and year in E. | Excel Discussion (Misc queries) | |||
| calculate month from week number | Excel Worksheet Functions | |||
| Convert week number into calendar month? | Excel Worksheet Functions | |||
| Insert Month and Year in my worksheet | Excel Worksheet Functions | |||
Linear Mode
