Calculate number of rows with current month & year in Google Sheets or Excel

Question

I have one row filled with dates in the format MM/dd/yyyy (with header row), such as:

DATES
5/12/2015
4/12/2012
5/7/2015
5/7/2014

I would like to count the number of rows which match the current month & year. In the example above, the result would obviously be two (when it's May 2015).

Is this possible to achieve this result using a function? Preferably using Google Spreadsheet, but Excel is also fine.

Answer 1

With data in A1 through A20

=SUMPRODUCT((MONTH(A1:A20)=MONTH(TODAY()))*(YEAR(A1:A20)=YEAR(TODAY())))

Answer 2

You can use COUNTIFS like this

=COUNTIFS(A:A,">="&EOMONTH(TODAY(),-1)+1,A:A,"<"&EOMONTH(TODAY(),0)+1)

That works equally well in Excel or google sheets, for the whole column and will ignore the header row

Answer 3

In Google sheets you can use

=COUNT(FILTER(A:A,MONTH(A:A) = MONTH(TODAY()),YEAR(A:A)=YEAR(TODAY())))

Where A:A is the column with your dates