Counting years in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-267863%22%20slang%3D%22en-US%22%3ECounting%20years%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267863%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20need%20a%20single%20cell%20in%20which%20the%20number%20of%20unique%20years%20is%20returned%20in%20a%20long%20list%20of%20dates%20in%20YYYY%2FMM%2FDD%20format.%20NOT%20a%20calculation%20of%20a%20number%20but%20a%20simple%20count%20of%20each%20year%20with%20a%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScott%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-267863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-267940%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20years%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267940%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20Detlef%3C%2FP%3E%3CP%3EIt's%20a%20really%20great%20function%20as%20it%20can%20solve%20many%20problems!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I've%20noticed%20that%20the%20SUM%20works%20here%20as%20well%20without%20the%20need%20to%20Ctrl%2BShift%2BEnter!%3C%2FP%3E%3CPRE%3E%3DSUM(--(FREQUENCY(YEAR(A1%3AA10)%2CYEAR(A1%3AA10))%26lt%3B%26gt%3B0))%3C%2FPRE%3E%3CP%3EThis%20is%20because%20the%20FREQUENCY%20is%20a%20powerful%20array%20function!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-267897%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20years%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267897%22%20slang%3D%22en-US%22%3E%3CP%3EGreat%20idea%20using%26nbsp%3BFREQUENCY().%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-267882%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20years%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267882%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Scott%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DSUMPRODUCT(--(FREQUENCY(YEAR(A1%3AA10)%2CYEAR(A1%3AA10))%26lt%3B%26gt%3B0))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Count%20Unique%20Years.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55698iACE98C22BF5ECF5A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Count%20Unique%20Years.png%22%20alt%3D%22Count%20Unique%20Years.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-267866%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20years%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267866%22%20slang%3D%22en-US%22%3Etry%3A%3CBR%20%2F%3E%3DSUMPRODUCT(1*(YEAR(A1%3AA13)%3DYEAR(A1)))%3CBR%20%2F%3Eyou%20can%20change%20the%20range%20A1%3AA13%20to%20suit%20and%20A1%20as%20the%20reference%20cell%20for%20specific%20year%3CBR%20%2F%3EHTH%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
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.