# Counting years in excel

Occasional Visitor

# Counting years in excel

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

5 Replies

# Re: Counting years in excel

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

# Re: Counting years in excel

Hi Scott,

Please try this formula:

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

Hope that helps

# Re: Counting years in excel

Great idea using FREQUENCY().

# Re: Counting years in excel

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!

# Re: Counting years in excel

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.