How do I create an "If" statement with two criteria involving the same column?
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 (02 pts)]@row:[Presentation/Product (02 pts)]@row, ISBLANK(@cell)) = 0, SUM([Project Approved (02 pts)]@row:[Presentation/Product (02 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

Try something like this:
=IF(COUNTIFS([Project Approved (02 pts)]@row:[Presentation/Product (02 pts)]@row, @cell = "") = 0, IF(SUM([Project Approved (02 pts)]@row:[Presentation/Product (02 pts)]@row)>= 7, "Yes", "No"))
Answers

Try something like this:
=IF(COUNTIFS([Project Approved (02 pts)]@row:[Presentation/Product (02 pts)]@row, @cell = "") = 0, IF(SUM([Project Approved (02 pts)]@row:[Presentation/Product (02 pts)]@row)>= 7, "Yes", "No"))

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

Happy to👍️ help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 359 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!