# SUMIFS to Calculate Values for a Specific Date Range, using cell value for Date Reference

Options

I have a formula that sums multiple statements, but the one I have I need help with is the one that references a range in dates.

=SUMIFS({Reference Hours}, {Reference Drafter}, [Primary Column]@row, {Reference Rework}, Rework1, {Date}, >=DATE(2023, 10, 26), {Date}, <=DATE(2023, 11, 1))

I want to be able to make the dates be updated using a cell.

I have two columns, one for Start Date and one for End Date, I want those to be the ones that drive the date range, otherwise I have to edit the formula every time.

How can I reference the cells on those columns inside the DATE formula?

• ✭✭✭✭✭✭
edited 11/06/23
Options

I assume the formula is going to be used on the same row as the start and end date that you want to use within it. If so, all you need to do is replace the hard coded dates with a reference to the cell.

Take this:

=SUMIFS({Reference Hours}, {Reference Drafter}, [Primary Column]@row, {Reference Rework}, Rework1, {Date}, >=DATE(2023, 10, 26), {Date}, <=DATE(2023, 11, 1))

And do this:

=SUMIFS({Reference Hours}, {Reference Drafter}, [Primary Column]@row, {Reference Rework}, Rework1, {Date}, >=[Start Date]@row, {Date}, <= EndDate]@row)

That will initially be unparseable due to an unrelated issue - it is missing double quotes around "Rework1".

This should be good:

=SUMIFS({Reference Hours}, {Reference Drafter}, [Primary Column]@row, {Reference Rework}, "Rework1", {Date}, >=[Start Date]@row, {Date}, <= EndDate]@row)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!