Excel SUMIFS Function to Sum Only for Dates In a Certain Month and Year?

Question

I have a range of dates in a worksheet that run across an entire row in format mm/dd/yyyy. As part of a SUMIF function I'd like to add up all the "points" that fall within a month/year combination (which would be specified via a cell reference). Any thoughts on how to do this? Should I instead try an If statement nested within SUM?

Answer 1

As @Scott Craner said, you can bookend the first and last dates of the month and then use a SUMIFS to add up all the "points".

Here's a simple example with a value of 1 in column B for every day in a month. Enter any date D3, and the formula in E3 will add up all the values in column B if the date in column A is between the first and last days of the month.

=SUMIFS(B:B,A:A,">="&DATE(YEAR(D3),MONTH(D3),1),A:A,"<="&EOMONTH(D3,0))

If you're using a text field in D3 instead of a date, you'd have to add DATEVALUE() to the formula to convert it to a date before extracting the first and last days of the month.

And here it is with the dates transposed into columns instead of rows. Same idea, but maybe harder to see in the screenshot.