# Need help with Formula

Options

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?

Tags:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

I would combine them into a single COUNTIFS like so:

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

That came up with an UNPARSEABLE error.

• ✭✭✭✭✭✭
Options

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

• Options
• Options
• ✭✭✭✭✭✭
Options

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.

• Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

I would combine them into a single COUNTIFS like so:

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

• Options

Awesome… Thanks so much for the help!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!