Counting Text Color
Hello, I am working on creating metrics in our Smartsheet milestones. Currently, if a milestone was completed ontime, the date completed is turned green. If the milestone was not completed ontime, 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 ontime 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")
Best Answer

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.
Answers

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

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.

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

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.

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

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!