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?
Best 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
-
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
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
-
The first one worked! Thank you!
-
Wonderful! Thanks for letting me know.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!