Rolling 30 day formula with specific text

Hello, I'm trying to get the past 30 days from today; on a Job type "IN" with a "completed" status.

I've looked all over for the answer but was unsuccessful. Every time I try I keep getting a failed or incorrect argument set. Any help would be apricated.



Best Answers

  • Khasim
    Khasim ✭✭✭✭✭
    edited 09/22/22 Answer ✓

    @Timothy ,

    No worries! I have another solution for you which you can apply in sheet summary. Please find the below formula & screenshot

    =COUNTIFS([Today- Scheduled]:[Today- Scheduled], <=30, Status:Status, "Completed", [Job Type]:[Job Type], "IN")

    Hope I answered your question


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: [email protected]

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • Khasim
    Khasim ✭✭✭✭✭
    Answer ✓

    @Timothy

    Your are welcome @Timothy. Kindly accept the answer and Vote-up. It will help other Smartsheet user like you😀


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: [email protected]

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

Answers

  • Khasim
    Khasim ✭✭✭✭✭

    Hi @Timothy

    Could you please elaborate? What are you trying to accomplish? It will be appreciated if you can provide an example.


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: [email protected]

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • Timothy
    Timothy ✭

    @Khasim I'm wanting to run a report to only show last 30 days of completed "IN" anytime I try to show last 30days.

    I've was doing something along the lines of

    =COUNTIFS(Scheduled:Scheduled, @row, >TODAY()(-30), [Job Type]:[Job Type], CONTAINS("IN"), Status:Status, CONTAINS("completed"))

    I just need to count the last completed installs last 30 days.

  • Khasim
    Khasim ✭✭✭✭✭
    edited 09/22/22

    Hi @Timothy ,


    The below Steps might help you.

    1.create new column to identify the difference between "Today- Scheduled" like in the below


    2.Create a report and apply filters such as job type as IN, Status as Completed, New column select as less than 30 days & Summarize the column (Scheduled) then it will give total count of last 30 days installs.



    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: [email protected]

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • Timothy
    Timothy ✭

    @Khasim Thank you for that idea and I'll give it a try but was avoiding to create another report and was hoping there was a way to create a formula to put this under the sheet summary for my dashboard.

  • Khasim
    Khasim ✭✭✭✭✭
    edited 09/22/22 Answer ✓

    @Timothy ,

    No worries! I have another solution for you which you can apply in sheet summary. Please find the below formula & screenshot

    =COUNTIFS([Today- Scheduled]:[Today- Scheduled], <=30, Status:Status, "Completed", [Job Type]:[Job Type], "IN")

    Hope I answered your question


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: [email protected]

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • Timothy
    Timothy ✭

    @Khasim Thank you so much! That worked and is very valuable.

  • Khasim
    Khasim ✭✭✭✭✭
    Answer ✓

    @Timothy

    Your are welcome @Timothy. Kindly accept the answer and Vote-up. It will help other Smartsheet user like you😀


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: [email protected]

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!