Selecting just Month and Year from date string (mm/dd/yyy) excel

0
votes

So I have a column of dates ranging from year 2014-2016 in format mm/dd/yyyy. 

    Date
    8/7/2014
    8/11/2014
    ...
    almost 600 lines, including all of year 2015, and into 2016

I am looking to have a formula count the number of occurrences of cells with the month = 1 and year = 2015, the day does not matter. This data will populate a table elsewhere in the spreadsheet. I can get it to tell me the number of occurrences of January's for all years, using:

=ArrayFormula(SUM(--(MONTH(D2:D) = 1)))

and i could restrict the range in column D to just the year 2015, but im trying to make this completely automated and self updating. so having it select by month and year is ideal. to try and add the month to that formula ive tried many things without success.

=SUMIF(D2:D,(AND(YEAR(D2:D) = 2015, MONTH(D2:D) = 1)))
=ArrayFormula(SUMIF(--(MONTH(D2:D) = 1),YEAR(D2:D) = 2015))

any help or suggestions would be greatly appreciated. Thank you!

1
excelgoogle-sheetsexcel-formula

1 Answers

1
votes

Try this:

=ArrayFormula(SUM((--MONTH(D2:D) = 1)*(--YEAR(D2:D)=2015)))