SUMIFS formula between two dates in a date column

Natye Briggs

I am new to the Smartsheet world and this maybe an easy formula for most of you. I need something that can sum data from one column (Class Total Hours) between a date range from a date column (Date of Participation). I attempted twice with two separate formulas and it provides me with 22.5 instead of 52.5 for the month of February. What am I doing wrong?

=SUMIFS([Class Total Hours]:[Class Total Hours], [Date of Participation]:[Date of Participation], >=DATE(2023, 2, 1), [Date of Participation]:[Date of Participation], <DATE(2023, 2, 28))

=SUMIFS([Class Total Hours]:[Class Total Hours], [Date of Participation]:[Date of Participation], AND(@cell >= DATE(2023, 2, 1), @cell < DATE(2023, 2, 28)))

In addition, how can I make this formula extract future dates? For Instance prepare the same formula to pull August data once it is entered.

Paul Newcome

The problem is that you are saying for the date to be less than the end of the month instead of less than or equal to.

<

vs

<=

My suggestion instead of hardcoding in specific dates is to evaluate for month and year like so:

=SUMIFS([Class Total Hours]:[Class Total Hours], [Date of Participation]:[Date of Participation], AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2023))

You would be able to just change the 2 and the 2023 to the appropriate month and year.

Paul Newcome

The problem is that you are saying for the date to be less than the end of the month instead of less than or equal to.

<

vs

<=

My suggestion instead of hardcoding in specific dates is to evaluate for month and year like so:

=SUMIFS([Class Total Hours]:[Class Total Hours], [Date of Participation]:[Date of Participation], AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2023))

You would be able to just change the 2 and the 2023 to the appropriate month and year.

Natye Briggs

Thank you! That worked, I appreciate you and your time.

Paul Newcome

Happy to help. 👍️