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!



Tags:

Answers

  • Kelly Moore
    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


  • RingJake
    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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!