Articles

Solved Excel Formula to count # Month-Year entries in a column

December 23, 2012 at 10:16:57
Specs: Windows XP

In a number of columns of my spead sheet I have Date columns with the format e.g. 12-Dec-12. I need to count the number of times Dec-12 shows up in the specific column. It doesn't matter what the day is but it is important that the Day and Year match. One of the columns is Colum A and data starts at A2 and ends at A35.

Thank you.


See More: Excel Formula to count # Month-Year entries in a column

Report •


#1
December 23, 2012 at 11:49:38
✔ Best Answer

Hi Callie

This solution assumes that your dates are in date format.

=COUNTIFS($A$2:$A$35,">=1/12/12",$A$2:$A$35,"<=31/12/12")

Essentially what this does is counts the dates higher than or equal to 1-12-12 and less that or equal to 31-12-12


Report •

#2
December 23, 2012 at 12:22:10

This worked perfectly, thank you so much.

Report •

#3
December 23, 2012 at 13:48:12

No problems - glad to help.

If you're going to need to do this every month there is a way to simplify it somewhat.

If you enter the first day of the month you want to count in a cell e.g. 1/1/2013 or
1/2/2013 (this is the date format in Australia - if yours is different eg M/D/Y you would enter 2/1/2013)

Then use this formula to calculate the number of occurrence for the month that you specified in, say, B1 and the formula in B2

=COUNTIFS($A$5:$A$100,">=" & $B$1,$A$5:$A$100,"<=" & EOMONTH($B$1,0))

It could look like this


         Column A                 Column B  
------------------------        ------------- 
Month you wish to count:	February 2013
                  Count:	       2
------------------------        -------------

       Dates	
     12-Dec-12	
     13-Dec-12	
     18-Dec-12	
     19-Dec-12	
     20-Dec-12	
     21-Dec-12	
     22-Jan-13	
     23-Jan-13	
     24-Jan-13	
     25-Jan-13	
     26-Jan-13	
     31-Jan-13	
     02-Feb-13	
     06-Feb-13	
     01-Mar-13	
     02-Mar-13	
     03-Mar-13	
     04-Mar-13	


Report •

Related Solutions

#4
December 23, 2012 at 19:26:52

thanks for the additional information as I will have to do this on a monthly basis.

Report •


Ask Question