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
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!