IF Formula Counting Multi-Select Drop Down Answers

Hello,

I am trying to create a formula using an IF statement that will say "On Track" if there is one answer in the cell, but if there are more than one it will output "Delay". I am assuming my IF statement is counting the answers the way it is working now, however it isn't quite counting correctly and not sure what I'm missing.

The formula should say "On Track" since there is only one answer in this cell, but instead it says "Delayed". Can somebody please take a look and help me out?

This will become a large OR statement with all of the other status abbreviations following the same format.

=IF(IB@row < 2, "On Track", "Delayed")

Thank you!

Screen Shot 2022-04-03 at 3.13.32 PM.png

Find more posts tagged with

Formulas

Accepted answers

All comments

Kelly Moore

Hey @RingJake

If I understand correctly, column IB is a multi-select dropdown column, that is, a cell that may have more than one answer within that single cell. If this is true, the COUNTM function will count the multi-select answers.

=IF(COUNTM(IB@row)<2, "On Track", "Delayed")

Does this work for you?

Kelly

https://help.smartsheet.com/function/countm

RingJake

Thank you @Kelly Moore this is working, now if I wanted to add the OR statement part, how would I write this properly? I will be adding 8 more cells following this logic in the IF OR statement.

This is my attempt, however I am getting an error.

=IF(OR(COUNTM(IB@row) < 2, "On Track", "Delayed", COUNTM(EB@row) < 2, "On Track", "Delayed"))

Kelly Moore

Hey

We can take advantage of your contiguous columns and go to a simpler route than an IF/OR

=COUNTIFS(IB@row:FRD@row, COUNTM(@cell) > 2)

To answer your question on the IF/OR syntax:

=IF(OR(criteria1, criteria2, criteria3, etc), "True", "False")

The OR closes when all of the ORs are added. They should all have the same True or False result.

Your example above shows

=IF(OR(criteria1, "True", "False", criteria2, "True", "False", criteria3, "True", "False")). This syntax is incorrect.

Let me know if you have any questions on any of the above.

Kelly

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.