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

lgabel

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!

Paul Newcome

Try something like this:

=IF(COUNTIFS([Project Approved (0-2 pts)]@row:[Presentation/Product (0-2 pts)]@row, @cell = "") = 0, IF(SUM([Project Approved (0-2 pts)]@row:[Presentation/Product (0-2 pts)]@row)>= 7, "Yes", "No"))

Paul Newcome

Try something like this:

=IF(COUNTIFS([Project Approved (0-2 pts)]@row:[Presentation/Product (0-2 pts)]@row, @cell = "") = 0, IF(SUM([Project Approved (0-2 pts)]@row:[Presentation/Product (0-2 pts)]@row)>= 7, "Yes", "No"))

lgabel

It worked! Thank you so much! Now I just need to figure out where I was making the mistake :-)

Paul Newcome

Happy to👍️ help.