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

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/23/22

    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 03/09/22

    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?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!