Conditional Formatting Metrics

I have a sheet with a lot of conditional formatting to identify when training and license dates in my sheet are coming close to expiration or have expired. The sheet has about 15 columns, each for a separate training or licensing completion date, and conditional formatting is looking at those dates to determine when it's past a certain number of days to shade it a certain color.

I'm now trying to generate a report or metrics to show how many people are within 180 days of expiration, 90 days within expiration, expired, etc. I know I can do this with formulas, but since I already have it in conditional formatting I wanted to ask if anyone knows of a way to generate the total number of cells that meet the criteria of a piece of conditional formatting logic. If I could get that number I could calculate the metrics I need, otherwise I think I'll have to create helper columns determining the number of dates to expiration, and then use a summary field to get a count of the number of records meeting the criteria of how close they are to expiration.


  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    From all my research I do not believe that is possible it would be a great addition. I have wanted to do something similar myself.

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @Bethany Garcia At the risk of being obvious, are you building a report of the sheet, or are you generating metrics? If the latter, where do you plan to make these metrics visible? I think the approach depends on your final desired outcome. I don't think you can count rows based on conditional format rules.

    I would probably use summary fields to count rows for your various conditions. (Not sure you need extra columns to help with that, although I often do employ such columns.) Then you could produce a summary report and display the results on a dashboard.

    Depending on how the sheet is laid out, you might be able to use a metric widget directly (doesn't seem like it from your description ... )


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!