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 (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!

Find more posts tagged with

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.