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.

 

Scott

5 Replies
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

Hi Scott,

 

Please try this formula:

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

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!

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

This is because the FREQUENCY is a powerful array function!

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.