Counting years in excel

[Scott Ranger]

I just need a single cell in which the number of unique years is returned in a long list of dates in YYYY/MM/DD format. NOT a calculation of a number but a simple count of each year with a date.

Scott

[#2]

try:
=SUMPRODUCT(1*(YEAR(A1:A13)=YEAR(A1)))
you can change the range A1:A13 to suit and A1 as the reference cell for specific year
HTH

[#3]

Hi Scott,

Please try this formula:

=SUMPRODUCT(--(FREQUENCY(YEAR(A1:A10),YEAR(A1:A10))<>0))

Hope that helps

[#4]

Great idea using FREQUENCY().

[#5]

Thank you, Detlef

It's a really great function as it can solve many problems!

Also, I've noticed that the SUM works here as well without the need to Ctrl+Shift+Enter!

=SUM(--(FREQUENCY(YEAR(A1:A10),YEAR(A1:A10))<>0))

This is because the FREQUENCY is a powerful array function!

[#6]

Try:
=COUNTIFS(A1:A10,">="&DATE(B1,1,1),A1:A10,"<="&DATE(B1,12,31))
Where [A1:A10] is your range of cells with dates to count and [B1] is the cell where you enter the year to count. You can also count months in a specific year or days in a month using this same formula, just change where the [B1] is in the [&DATE] part.