Sum of Cells when another Cells Fall In a Certain Date Range
Hello Smartsheet community!
I have a sheet that has a column for a Delivery Date, and another column for the Qty of items to be delivered.
Delivery Date - Column A
Qty of Carrots - Column B
I need to put together Summary Data to know how many Carrots are needed for a specific date range, example the month of June?
Thanks in advance for the assist!
Answers
-
=SUMIFS([Qty of Carrots]:[Qty of Carrots], [Delivery Date]:[Delivery Date], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 1, 31)))
The hardest thing about that is with Dates, "SUMIFS" and "AND" statements all together it can do some weird things.. it gets harder if you want to say things like = "Jan"
This is where cheater columns might help to take the months and years out of the date.. Then you can say things like CheaterMonth = "Jan"
You could also look at reporting if you are doing forecasting.. the new grouping gets pretty powerful
That works easier but I had to create a cheater column for the Month so it sorts in the correct order and is readable by month.
This is using the sum of Qty of Carrots Grouped by the "Cheater Column"
-
Thanks for the info. The ask was for creating Summary Data to be used on a Dashboard, so I'll have to keep searching. Thanks!
-
Charts can now be created using the Grouping and Summary feature in a Report, so the above Reporting suggestion may work for you now!
See the announcement post here.
Cheers,
Genevieve
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!