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

 

[jbeaucaire]

Maybe this:

=SUMPRODUCT(--(MONTH($A$1:$A$50)=1))


When you edit this, resist the urge to use $A:$A. Use a specific but sufficient range.

 

[Richard Schollar]

If your range will include blanks then you will want to amend Jerry's formula slightly as blanks will return a 1 inside the MONTH() function:

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

or:

=SUMPRODUCT(--(TEXT($A$1:$A$50,"mmm")="Jan"))

 

[Rossjp]

I'm using Excel 2003, sorry I forgot to mention that. Will the formula you gave work with 2003?

 

[Rossjp]

Jerry's formula seems to return the correct count regardless of blank cells EXCEPT for the month of Jan (1) when it does count the blank cells????

 

[Richard Schollar]

Jerry's formula seems to return the correct count regardless of blank cells EXCEPT for the month of Jan (1) when it does count the blank cells????

Yes that's what I said

I gave you two alternative ways of dealing with this - both should work as desired.

 

[Rossjp]

When I tried your formula for January it failed to count january dates in the date format 1/1/10

 

[sthack99]

Try this:

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

 

[Rossjp]

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="")))

 

[sthack99]

What is between those two ranges? There's no way to consolidate them into one range?