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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!