# Counting Months only in Excel



## PincivMa (Mar 14, 2004)

Hi again

This time I've encountered another problem with dates. Below are a series of dates in one column formatted mmm-yy. I have many of these dates ranging from Jan-05 to Dec-05 and a few in Jan-06. I want to have a count of how many Jan-05 I have, how may Feb-05 I have etc. I want a number count. for example I have 2 May-05 and 8 Sep-05. The countif formula does not work since it requires the full date, i.e. May 12, 2005 etc. But then it only gives me 1 count only, since the other May-05 has a different date. All I want is to count the months only. Any idea how to do this?? Do you have to write a macro to accomplish this task??

Mario


May-05
May-05
Jun-05
Jun-05
Jul-05
Jul-05
Aug-05
Aug-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05
Sep-05


----------



## Anne Troy (Feb 14, 1999)

Nope. You can do it. See this:
http://www.officearticles.com/excel/sumif_with_dates_in_microsoft_excel.htm


----------



## Yorkshire Guy (Dec 9, 2003)

Hey Dreamboat,

Have a look at an alternative solution, attached, using an Array Formua!
I took a challenge against the Office Article, he he.

PincivMa,
If you want to do it this way, note that the formula in col D is an ARRAY FORMULA, you key it as shown without the surrounding { } then instead of ENTER, press CTRL+SHIFT+ENTER at the same time.

Col C was entered as 1/1, 1/2, 1/3, 1/4...... then formatted as MMM for display.
(I'm in the UK so those were 1-Jan, 1-Feb, 1-Mar....)

lol
Hew


----------



## Dreambringer (Jan 19, 2005)

Couldnt you just add another Column and enter 1 for each cell, hide the cell and then create a pivot chart? 

Its nothing fancy like these guys but it gets the job done... check my sample.


----------



## maxflia10 (Feb 25, 2003)

Try,

=SUMPRODUCT(--(DATE(YEAR($A$1:$A$10),MONTH($A$1:$A$10),1)=B1))

Where B1 houses a date which month/year you're trying to count.


----------



## Anne Troy (Feb 14, 1999)

LOL. My Office Articles aren't really THE answer, but they're intended to point someone in the right direction.


----------



## Anne Troy (Feb 14, 1999)

maxflia10 said:


> Try,
> 
> =SUMPRODUCT(--(DATE(YEAR($A$1:$A$10),MONTH($A$1:$A$10),1)=B1))
> 
> Where B1 houses a date which month/year you're trying to count.


Brian: Someday you're going to have to teach me about those -- formulas.


----------



## maxflia10 (Feb 25, 2003)

Dreamboat said:


> Brian: Someday you're going to have to teach me about those -- formulas.


You and Scott come visit me!


----------



## Anne Troy (Feb 14, 1999)

I wish!!


----------



## Zack Barresse (Jul 25, 2004)

Not sure if it makes a difference, but this is how I do it ...

=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")=TEXT(B1,"mmm-yy")))

.. I have some of the same calculations on one of my workbooks.

I have a short article on such a technique..
http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=42

I'll visit ya Brian, when I get enough time/money.


----------

