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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!