Need help with Formula

Mike Deeeez

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?

Paul Newcome

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.

Paul Newcome

I would combine them into a single COUNTIFS like so:

COUNTIFS([1st Column]@row:[Last Column]@row, OR(@cell = "Resign", @cell = "Term"))

Mike Deeeez

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

Paul Newcome

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

Mike Deeeez

That came up with an UNPARSEABLE error.

Paul Newcome

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

Mike Deeeez

Mike Deeeez

Paul Newcome

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.

Mike Deeeez

@Paul Newcome

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"

Mike Deeeez

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.

Paul Newcome

I would combine them into a single COUNTIFS like so:

COUNTIFS([1st Column]@row:[Last Column]@row, OR(@cell = "Resign", @cell = "Term"))

Mike Deeeez

Awesome… Thanks so much for the help!

Paul Newcome

Happy to help. 👍️