How do I create a COUNTIF formula that counts a status as "closed", but only if within 90 days?

Jeffrey Metivier

Appreciate any help you can provide. Thanks!

David Tutwiler

You'd need to look at both columns to make this determination, and use a COUNTIFS formula. Something like:

=COUNTIFS(status@row, "closed, date@row, >(TODAY() - 90))

Jeffrey Metivier

Unfortunately we don't have a column that tracks the date the status changed to CLOSED. We just have the status column itself.

Kelly Drake

You have to have the data point for the formula to evaluate in the first place, otherwise there's no way for the formula to function the way you're trying to.

If no further edits would be made after the status changes to closed, you can add a system column of last updated. If however there will be future edits, you'll need to manually add a date.

David Tutwiler

I'm not sure how you'd verify if it's closed within 90 days if you don't have some type of date field to check against. You would need either the close date or some type of System date of when the row was entered. Otherwise, there is nothing for the formula to check against to filter out newer/older counts.

Andrée Starå

Hi @Jeffrey Metivier

To add to David's and Kelly's excellent answers.

Please have a look at my post below with a method I developed that you could use to get the date to check against.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!