Counting years in excel

Occasional Visitor

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.



5 Replies
you can change the range A1:A13 to suit and A1 as the reference cell for specific year

Hi Scott,


Please try this formula:


Count Unique Years.png


Hope that helps

Great idea using FREQUENCY().

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!


This is because the FREQUENCY is a powerful array function!

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.