Counting the instances of months in a range of dates

[montek]

I have a column of dates formatted mm/dd/yyyy on one sheet. On a second sheet in the workbook I am trying to keep a running count of the number of times January, February, March, etc... are entered. I have this below and it seems to work except for January. I get a count of 751, all of the blank cells. If I type a date in January 751 reduces to 750. For February, March, etc. it works fine.

=SUMPRODUCT(--(MONTH(INDIRECT(AB18))=1))

AB18 is Papers!$C$4250:$C$5000 for the other sheet and cell range.

Thanks for any help!

[Palmetto]

Try this array formula, copied down twelve rows.

{=COUNT(IF(MONTH(Sheet1!$A$1:$A$37)=ROW(A1),Sheet1!$A$1:$A$37))}

Adjust the cell references in the formula for the cells containing the dates.

Array formulas are committed using the Ctrl + Shift + Enter keys in combination.

[Haseeb Avarakkan]

Try like this,

=SUMPRODUCT(--(TEXT(INDIRECT(AB18),"mmm;;")="Jan"))

If you need to use full month like, "January", "March", "July" etc.. use 4 mmmm

[DonkeyOte]

...it seems to work except for January. I get a count of 751, all of the blank cells. If I type a date in January 751 reduces to 750. For February, March, etc. it works fine.

To explain the results you're seeing with your original

In XL Dates are Serial Numbers
In XL true Blanks equate to 0
On 1900 Date System 0 in Date Terms equates to 0 Jan 1900

Hence when you test the Month of a blank it will be 1 (January)

Haseeb's approach circumvents the above by virtue of a Custom Format (0's are handled such that they do not return Jan) ... avoiding use of explicit coercion is also no bad thing IMO (eg TEXT vs MONTH)

[montek]

Thanks to all who replied. I tried the text function, but I didn't use the "mmm", adding that, it works perfectly.

I racked my brain (what little there is) for quite a while and I couldn't figure out why it was counting the blank cells, thanks for explaining that one.

This problem is solved. Thanks!