Conditional Formatting Metrics

Bethany Garcia

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

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

@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 ... )

dm