# SUMIFS formula between two dates in a date column

✭✭✭
edited 07/06/23

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.

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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.

• ✭✭✭

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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!