# Count how many tickets have been completed before the due date

Options
✭✭

Hi,

I'm trying to create a formula metric to count the number of instances that a ticket has been resolved before the due date has expired. I'm a bit lost how to use =COUNT(IF([Due Date]>[Resolved],1,"")). Sorry new to smartsheet appreciate any help.

Thanks

• ✭✭
Options

Hi Aya

Sorry for the late reply but I have it solved thanks to your help and have some other data that may be useful later. I created the column

SLA Remaining like you suggested and that has

=[Due Date]@row - Resolved@row in it.

Then I created a Completed On time column that basically checks if SLA Remaining is <0 so a negative or not with and puts a yes or no

=IF(NOT(ISBLANK(Resolved@row)), IF([SLA Remaining]@row < 0, "No", "Yes"))

Then in my metric Tickets Resolved within SLA it counts the YESs in Completed on time

=COUNTIF({Completed Ontime}, {Completed Ontime} = "Yes")

Not very elegant but it has done the job. Thanks again for your help and pointing me in the right direction.

• ✭✭✭
Options

HI Awesomas,

You might need to create a formula column that would allow you to see the difference between the Due Date and Resolved Date:

```=[Due Date]@row-[Resolved]@row
```

Then, on your COUNTIFS formula you can use that formula column as one of your range and have something like this:

```=COUNTIFS({Ticket# Column},<>"", {Formula Column}, >0)
```

Let me know if this helps or if you have questions.

Cheers!

-Aya

• ✭✭
Options

Hi Aya

Sorry for the late reply but I have it solved thanks to your help and have some other data that may be useful later. I created the column

SLA Remaining like you suggested and that has

=[Due Date]@row - Resolved@row in it.

Then I created a Completed On time column that basically checks if SLA Remaining is <0 so a negative or not with and puts a yes or no

=IF(NOT(ISBLANK(Resolved@row)), IF([SLA Remaining]@row < 0, "No", "Yes"))

Then in my metric Tickets Resolved within SLA it counts the YESs in Completed on time

=COUNTIF({Completed Ontime}, {Completed Ontime} = "Yes")

Not very elegant but it has done the job. Thanks again for your help and pointing me in the right direction.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!