Formula for COUNTIFS and Dynamic Date Range
Hi Smartsheet Community,
I am really struggling to get the right formula and have tried so many now I am losing it! I am trying to prepare dashboards for weekly project meetings based on SLAs. I need to look at the count sitting in a particular status that have a Contract Date of >28 days, 14-28 days and <14 days. Because it is a dashboard, I need the date ranges to be dynamic, therefore am trying to utilize the TODAY function, but I cannot get the right calculations or avoid #UNPARSABLE errors.
=COUNTIFS({Status}, "1. Contracted", {CW Date}, >TODAY(-28))
Here is my source:
Here is my summary sheet I am working on for the dashboard - I tried adding helper columns with today's date and then calculated the 28-day and 14-day dates based off today's date and using a formula, but I cannot figure it out
=COUNTIFS(({CW Date}, >Dates8, {Status}, "1. Contracted")
Do you have any suggestions for another formula, or am I on the right track and just have something off in my formula?
Best Answer
-
Try:
=COUNTIFS({Status}, "1. Contracted", {CW Date}, @cell >= TODAY(-28))
Answers
-
Try:
=COUNTIFS({Status}, "1. Contracted", {CW Date}, @cell >= TODAY(-28))
-
Thanks @Nic Larsen - That worked!
-
Glad it worked. If you need to do a range, it would be something like this at the end:
AND(@cell >= TODAY(-7), @cell <= TODAY()
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 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
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!