Find how many different months in a column of dates

[i0mega]

Hello, I am trying to get a formula to count how many Septembers, Octobers etc in a column of dates. I found this one online but its giving me strange results.

this is column A


Months 9 (Sept) and 10 (Oct) are correct but there are Januarys listed but its counting 251. Any ideas?
Thank you

Month​	Qty​
9​	7​
10​	5​
11​	0​
12​	0​
1​	251​
2​	0​
3​	0​
4​	0​
5​	0​
6​	0​
7​	0​
8​	0​

 

[Skovgaard]

Hello, I am trying to get a formula to count how many Septembers, Octobers etc in a column of dates. I found this one online but its giving me strange results.
View attachment 75699 this is column A


Months 9 (Sept) and 10 (Oct) are correct but there are Januarys listed but its counting 251. Any ideas?
Thank you

Month​	Qty​
9​	7​
10​	5​
11​	0​
12​	0​
1​	251​
2​	0​
3​	0​
4​	0​
5​	0​
6​	0​
7​	0​
8​	0​

Are there data with January for many years, or only 2022?

/Skovgaard

 

[Fluff]

I found this one online

What is the formula you found?

Chances are that the formula is counting blank cells as Jan.

 

[SanjayGMusafir]

Januarys listed but its counting 251

this error can be for many reasons with dates - Depends how you are populating counts.

If you can share some sample data using XL2BB instead of screenshot that would help identifying the problem

 

[i0mega]

Sorry I thought I had put the formula on
SUMPRODUCT(1*(MONTH(A$3:A$265)=$J6))
Currently the dates are just Sept and ocotober for now, nothing after 6th October

 

[i0mega]

What is the formula you found?

Chances are that the formula is counting blank cells as Jan.

You are right I have just tallied them up, it seeing blanks as January
SUMPRODUCT(1*(MONTH(A$3:A$265)=$J10))

 

[SanjayGMusafir]

You are right I have just tallied them up, it seeing blanks as January

See if it works -

=SUMPRODUCT((MONTH(A$3:A$265)=$J6)*(A$3:A$265<>""))

 

[Fluff]

Thanks for that, how about

=SUMPRODUCT((a3:a265<>"")*(MONTH(A$3:A$265)=$J10))

 

[i0mega]

Great stuff, thanks guys, its adding up now

 

[SanjayGMusafir]

Great stuff, thanks guys, its adding up now

Glad to help you