Need help with Formula
Here is my formula to track Corrective Action status based on total attendance points.
=IF([Total attendance points]@row > 7.9, "TERM", IF([Total attendance points]@row > 5.9, "FINAL", IF([Total attendance points]@row > 3.4, "WRITTEN", IF([Total attendance points]@row > 1.9, "VERBAL", IF([Total attendance points]@row < 1.6, "NONE")))))
I am wanting to add another rule that does not have to do with attendance points, but when a cell in a row = "Term" then the Corrective action status will populate "NLE" (which is no longer employed).
Is this even possible?
Best Answers

Based on the lack of colors in the column names, I would start with double checking that your column names are spelled correctly to include spaces. Look out for hidden spaces. Smartsheet will only display a single space even if you have 15 between two words. The safest way to take care of this part is to click into each column while writing the formula to have SS automatically enter the column name for you.

I would combine them into a single COUNTIFS like so:
COUNTIFS([1st Column]@row:[Last Column]@row, OR(@cell = "Resign", @cell = "Term"))
Answers

So I tried one individual day and the formula worked
=IF([Mon1/1]@row = "Resign", "NLE", IF([Total attendance points]@row > 7.9, "TERM", IF([Total attendance points]@row > 5.9, "FINAL", IF([Total attendance points]@row > 3.4, "WRITTEN", IF([Total attendance points]@row > 1.9, "VERBAL", IF([Total attendance points]@row < 1.6, "NONE"))))))
But if I added the entire row, then I get an INVALID OPERATION error.
=IF([Mon1/1]@row:[Tue12/31]@row = "Resign", "NLE", IF([Total attendance points]@row > 7.9, "TERM", IF([Total attendance points]@row > 5.9, "FINAL", IF([Total attendance points]@row > 3.4, "WRITTEN", IF([Total attendance points]@row > 1.9, "VERBAL", IF([Total attendance points]@row < 1.6, "NONE"))))))

Try using a COUNTIFS.
=IF(COUNTIFS([Mon1/1]@row:[Tue12/31]@row, @cell = "Resign") > 0, "NLE", IF([Total attendance points]@row > 7.9, "TERM", IF([Total attendance points]@row > 5.9, "FINAL", IF([Total attendance points]@row > 3.4, "WRITTEN", IF([Total attendance points]@row > 1.9, "VERBAL", IF([Total attendance points]@row < 1.6, "NONE"))))))

That came up with an UNPARSEABLE error.

Can you provide a screenshot of it open in the sheet as if you are about to edit it?



Based on the lack of colors in the column names, I would start with double checking that your column names are spelled correctly to include spaces. Look out for hidden spaces. Smartsheet will only display a single space even if you have 15 between two words. The safest way to take care of this part is to click into each column while writing the formula to have SS automatically enter the column name for you.

Thanks I found the error, and it works perfectly. How do I combine the 2 criteria below?
(
COUNTIFS([Mon1/1]@row:[Tue12/31]@row, @cell = "Resign") > 0
, "NLE"(
COUNTIFS([Mon1/1]@row:[Tue12/31]@row, @cell = "Term") > 0
, "NLE" 
So in short, I would like the cell with the formula to produce "NLE" if one or more of the cells if that row have "Resign" or "Term" selected.

I would combine them into a single COUNTIFS like so:
COUNTIFS([1st Column]@row:[Last Column]@row, OR(@cell = "Resign", @cell = "Term"))

Awesome… Thanks so much for the help!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!