How to count occurrences of each year in Excel?

Question

I want to count which cells have a specific year in their date/string. I have a problem where my formula only works if it's a valid date, some cells have month or day missing or are totally blank.

Here are some examples of values I want to be able to count:

2002-07-?
2010-11-27
2009-10-21
2009-10-21
2004-12-20


2004-11-07
2010-11-?
2004-09-17
2000-?-?
2005-04-26

This is how I want the output to be:

Unknown 2
2000    1
2001    0
2002    1
2003    0
2004    3
2005    1
2006    0
2007    0
2008    0
2009    2
2010    2

If I use =COUNTIF(A1:A12;"2000*") I only get those cells which are strings. Is there a way I could count both dates and strings?

Answer 1

Use a helper column and use the following formula to extract the year:

=IF(ISTEXT(A1);LEFT(A1;4);TEXT(A1;"YYYY"))

Then use your existing =COUNTIF() formula but without the wildcard * argument:

=COUNTIF(A1:A12;"2000")

Answer 2

Haven't got Excel to hand to test this, but I believe you can have another column that converts the value to text- I think it's =TEXT(A1,"<format>"), then just do your 'countif' on that.

EDIT: Forgot about the second argument. I'm surprised it didn't work with the 'yyyy' argument though.