Counting Text Color

mbreidenstein

Hello, I am working on creating metrics in our Smartsheet milestones. Currently, if a milestone was completed on-time, the date completed is turned green. If the milestone was not completed on-time, the date completed is turned red. What I would like to do is create a formula for metrics that reads the text color for how many on-time vs late milestones. I keep seeing that there is a limitation to this and the formulas can only identify green or red symbols and not text. Since we report out on each milestone's metric, including the date (we track the date for a different report), we can't use just symbols. Is there a way of creating a formula that would work in this situation? It seems easy enough in my head but nothing seems to be working.

Below is the idea - the column reading data is JT Write Up Complete and if the date is "Green", how many date are colored "Green". I'm probably over complicating this but any ideas or assistance is greatly appreciated.

=COUNTIF([JT Write Up Complete]:[JT Write Up Complete], "Green")

mbreidenstein

Hello heyjay!

Thank you for your help! I ended up adding a symbols column and fed the metrics from that. It actually turned out to be easier than I thought and wasn't an overwhelming addition.

heyjay

I know any function that could identify the font color. What you can do is that if you have a formula that is turning the cell color, use that instead to count. Then create a Count if completed on time otherwise count if not completed on time formula.

mbreidenstein

Hello heyjay!

Thank you for your response! I do not have a formula that is turning the cell text red or green. We did try to create one but it wouldn't function the way we needed it to so as a workaround, we made changing the cell text color a manual step since we are already going into the cell to enter the completed date. We also tried conditional formatting but we have 60 columns and it just wasn't able to work.

I know I could insert a symbol column after each completed date column but with 60 columns, I would like to limit how overwhelming the trackers can be and limit the amount of manual effort being done - if possible.

heyjay

It will be a pain to set up, but it will help in the long run specially if you are planning to use this for a long time. Use the clone option.

Do you have a Due Date and Completed Date column? You can probably use that in the formula.

=Countif ( completed date < due date ). Then you can do a sum function.

mbreidenstein

That's what I am thinking. We want to replace the 10 different sources we are currently using/tracking/updating with Smartsheet. This is the last piece to make it work. So if that's what it comes down to, I can set it up.

We do have the Due Date and Completed Date columns.

heyjay

Add a helper column to identify the status. Use the status column to count the number of items you want.

Status formula =IF([JT Write Up Due]@row > [JT Write Up Complete]@row, "OK", "Past Due")

Count Formula =COUNTIF(Status1:Status3, "OK")

mbreidenstein

Hello heyjay!

Thank you for your help! I ended up adding a symbols column and fed the metrics from that. It actually turned out to be easier than I thought and wasn't an overwhelming addition.