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?

3

1 Answer

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.

4

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy