Countif on dates (month and year)

**GrahamUK33** · 07-01-2010, 04:46 PM

I would like to count the amount of times 06/2010 appears in a list.

01/06/2009
15/05/2010
13/06/2010
20/06/2010
01/07/2010

So the value I am looking for on the example is 2

I have managed to get this formula, but not sure if it is anywhere near what I’m after:
=COUNTIF((H3:H212),"06,2010")

**DonkeyOte** · 07-01-2010, 04:51 PM

Depends on your version really ...

XL2007+

=COUNTIFS(H3:H212,">="&DATE(2010,6,1),H3:H212,"<"&DATE(2010,7,1))

Pre XL2007

=COUNTIF(H3:H212,">="&DATE(2010,6,1))-COUNTIF(H3:H212,">="&DATE(2010,7,1))

or

=SUMPRODUCT(--(TEXT(H3:H212,"MMYYYY")="062010"))

Else consider using a Pivot Table

**pb71** · 07-01-2010, 04:51 PM

With your dates in A1 to A5 for example:

=SUMPRODUCT((TEXT(A1:A5,"mm-yyyy")="06-2010")*1)

EDIT: Too slow, beaten to it - DonkeyOte have you had 3 Weetabix?