samedi 28 mars 2015

How to count years in Excel?

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?


Aucun commentaire:

Enregistrer un commentaire