Last 7 Days
I need a formula that counts the number of requests that have been submitted within the last seven days if assigned to "John Doe".
Smartsheet's AI generated this formula, however it comes back to me as #UNPARSEABLE.
=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, {Filing Submitted Date}@cell>=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")
Thoughts?
Answers
-
This part in bold doesn't look right.
=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, {Filing Submitted Date}@cell>=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")
Can you try
=COUNTIFS({Filing Submitted Date}:{Filing Submitted Date}, >=TODAY(-7), {LRT Assigned To:}:{LRT Assigned To:}, "Drake Detwiler")
If you really want John Doe, you also need to put that in place of Drake Detwiler
-
Looks like that came back as #UNPARSEABLE too, unfortunately. 😔
Also, the name doesn't have to be John Doe. That was just used as an example.
-
Sorry, I wasn't paying enough attention to the brackets. The column references are also incorrect. You can either reference a column in another sheet using {reference name} or reference a column in the current sheet using [column name]:[column name]. You cannot use {something}:{something}.
So your formula will be either
=COUNTIFS({Filing Submitted Date}, >=TODAY(-7), {LRT Assigned To:}, "Drake Detwiler")
If it is in different sheet to the data
or
=COUNTIFS([Filing Submitted Date]:[Filing Submitted Date], >=TODAY(-7), [LRT Assigned To:]:[LRT Assigned To:], "Drake Detwiler")
If it is in the same sheet as the data
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 368 Global Discussions
- 202 Industry Talk
- 433 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 280 Events
- 31 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!