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

Solution 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.