Counting number of times a specific month appears in a range

[Rossjp]

A column in my spreadsheet contains the actual date that a task was completed during a specific month. For each month I want to calculate the number of times that the cells in the range contain a date specific to that month.

Column A for the month of January

1/3/10
1/7/10
1/30/10
2/3/10
2/6/10

?? formula to count the number of times a date for the month of January occurs in this column

Results would be 3

Any help will be appreciated

 

[Richard Schollar]

Sorry - I forgot to wrap brackets round the second argument:

=SUMPRODUCT(--(MONTH($A$2:$A$50)=1),--($A$2:$A$50<>""))

You could have used the Text function formula (second one I gave) which I personally like because it is very easy to read & understand

 

[Rossjp]

There is text and some blank cells. Consolidation of the rows between the two ranges isn't possible without losing the ingegrity of the spreadsheet as it is used and as I've used it for the past 4 years.

 

[sthack99]

No, what I mean is....try this:

=SUMPRODUCT(--(MONTH($M$155:$M$215)=1),--($M$155:$M$215<>""))

(I'm using Richard's formula since it's cleaner and works for all months.)

 

[Rossjp]

thanks - the previous formulas actually worked when I got the ranges correct!. Sorry!

thank you for your help and patience.

 

[Aladin Akyurek]

Usingthe forst range in the formula below seems to product the desired result - not counting blanks but counting all dates for january formatted as 1/1/10.

However,when I use the formula as below the second series (after the + sign) is apparently not calculating.

BTW the first range is for the current Month (Jan) and the second range picks up any Januarydates in the previous month's (Dec) column as some Dec items are not finalized until Jan.

=SUMPRODUCT(--((MONTH($M$195:$M$215)=1)-($M$195:$M$215="")))+SUMPRODUCT(--((MONTH($M$155:$M$185)=1)-($M$155:$M$185="")))

Try...

=SUMPRODUCT(--($M$195:$M$215-DAY($M$195:$M$215)+1=DATE(2010,1,1)))