SUMIFS to Calculate Values for a Specific Date Range, using cell value for Date Reference
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?
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!