Formula for requests due in a certain time frame.
Hi,
I'm trying to create a COUNTIFS formula for my metrics sheet.
=COUNTIFS({Creative Services Tracker Status}, <>"05_COMPLETE", {Creative Services Tracker Status}, <>"06_DEAD", {Creative Services - Requested Completion}, <=TODAY(7))
I'm trying to see a count of requests that are due within the next 7 days that DO NOT have a status of "05_COMPLETE" or "06_DEAD". The number that counts based on this formula (17), does not match with the number of requests listed on the report I built (10).
My report has these conditions:
Status is not one of "05_COMPLETE", "06_DEAD"
and
Requested Due Date is in the next 7 days
Any thoughts?
Thanks,
Kaitlyn
Answers
-
Hey @Kaitlyn Carroll
Is the report capturing any blanks?
-
Hi @Kelly Moore,
I didn't think about that. I just checked - I didn't have it filtered to exclude blanks but I just added that because I don't want it to capture blanks. Thanks for asking that clarifying questions.
-
Hey Kaitlyn
Did you ever figure out the source of the difference between the report and the formula?
I wondered if this worked
=COUNTIFS({Creative Services Tracker Status}, OR(@cell<>"05_COMPLETE", @cell<>"06_DEAD"), {Creative Services - Requested Completion}, <=TODAY(7))
-
Unfortunately, no. I never figured it out. Everything appears to line up, but the numbers are different.
-
Is it possible to show a screenshot of both sheet and also the criteria for your report?
-
Absolutely - great idea.
Here is the formula on the metrics report:
This formula is equaling to 9.
Here is screenshots of the report criteria:
And the report populates 12 requests right now.
-
Hey Kaitlyn
Your formula says that the criteria is less than TODAY(), not TODAY(7) as indicated in the formula I suggested. The 7 inside the brackets indicates the date is 7 days from today.
Please advise if the addition of TODAY(7) remedies the difference between report and calculation.
Kelly
-
When I add TODAY(7), the count is then 21. And the report is still at 12.
-
Kaitlyn
When I look at your report the responses are not identical to the responses in your sheet. Your report shows all caps, this is not what is in the sheet. Has the column changed over time and the report is still looking at an older version of the column?
Scroll down to the very bottom of the column section of the report and make sure the column name does not have a red mark by it
Also, does your DATE column have dates in all the cells. What happens if you add another condition to the report where the Date is a date?
Kelly
-
Hi @Kelly Moore,
Very good catch on the all caps. I did not see any red marks, but I decided to create a new report just to cover all the bases. The new report pulled the same number as the old one I was working with. I'm not sure how to create a condition that the DATE column must equal a date (but I did check that all cells under the date column are populated with a date.
Do you think it's the formula?
-
Hey
In case you need this in the future, here is how to add an IS DATE condition to a report. It only works on date columns.
I should have thought of this earlier - whenever troubleshooting a formula one can remove terms in the formula, one by one, to see if a single term is causing the error. I suggest we do this between the report and the formula to see if we can get the data to match. This is only temporary for trouble shooting purposes.
I suggest to first remove the date portion from both the report and formula and see if the numbers match. If they do not, remove the next term. If that matches, we'd put the date back. We keep messing with formula and report to see if we can isolate the problem.
-
Hey Kaitlyn
Is it possible to get a screenshot of your {Creative Services Tracker} sheet. One more question that I should have asked in the beginning. Is that Status column a multi-select dropdown - or a single select?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!