Thread: Counting years

I have a column of dates entered in the format dd/mm/yyyy.
I want to count how many times a certain year occurs.
I have knowledge of formulas but not of writing vb code.

Hello tracey

If your dates are in A2:A100 and you have the year to count in C2, e.g. 2008, then use this formula in D2

=SUMPRODUCT((YEAR(A$2:A$100)=C2)+0)

copy down for more years listed in C3, C4 etc.

Fantastic!
Works like a dream

Thanks

I now have two columns:

One in date format (dd/mm/yyyy)
One in text format for systems (eg D3100CTW, D3300FTW etc)

I need to count, for example, how many D3100 systems were made in 2008
I can do this seperately but trying to put the two together just gives me a #value error
I've tried AND and COUNTIFS functions but still get the same error
The target cell is formatted as general

COUNTIFS function is only available in Excel 2007. If you have that version then you could accomplish it with COUNTIFS but you can't check for the year in the same way so it would be a little like this

=COUNTIFS(A$2:A$100,">="&DATE(C2,1,1),A$2:A$100,"<="&DATE(C2,12,31),B$2:B$100,D2&"*")

where A2:A100 contains dates, B2:B100 contains the system data, C2 is a specific year like 2008 and D2 is system type e.g. D3100

To do the same in any version of excel you can extend the SUMPRODUCT formula like this

=SUMPRODUCT((YEAR(A$2:A$100)=C2)*(LEFT(B$2:B$100,LEN(D2))=D2))