How do I create an "If" statement with two criteria involving the same column?

lgabel ✭✭✭
edited 02/22/24 in Formulas and Functions

I am working on a rubric that faculty can score for microcredential awards. There are 5 scoring criteria, then a column for "total" score, and then a "yes"/"no" dropdown for "Meets credential requirements." I've set up the total score to only show if none of the criteria columns are blank:

=IF(COUNTIF([Project Approved (0-2 pts)]@row:[Presentation/Product (0-2 pts)]@row, ISBLANK(@cell)) = 0, SUM([Project Approved (0-2 pts)]@row:[Presentation/Product (0-2 pts)]@row), "")

and if the score is 7 or higher, the dropdown changes to "yes." :

=IF([Total Score]@row <> "", "Yes", "No")

The problem is as soon as I start entering scores for the criteria, the dropdown goes to "no" because the total score hasn't reached 7 yet, even though the score isn't visible yet. "No" triggers a bunch of other automations. How do I combine the formulas above so that the Yes/No only shows once all the scoring criteria are not blank?

I'm two weeks into Smartsheet and this is above my skill level!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!