Rolling 30 day formula with specific text

Timothy

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.

Khasim

@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

Khasim

@Timothy

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

Khasim

Hi @Timothy

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

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

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.

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

@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

Timothy

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

Khasim

@Timothy

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