Formula to count of items in a multi dropdown list

24

Answers

  • What would the formula look like if you were checking a multi-select column to see if it contains two options?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Please reference the last post in the thread. That should be it.

    thinkspi.com

  • Thank you! That one counts ALL the cells that have either of those values, I only want it to count if it has BOTH in the same cell. Does that make sense?

    Also did not know that varied by country, very cool!

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this...

     

    =COUNTIFS([Column Name]:[Column Name], AND(CONTAINS("Option 1", @cell), CONTAINS("Option 2", @cell)))

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Have a fantastic weekend!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I want the same functionality but for if and not countif.

    So if someone selects one value from the drop down, it should reflect as 1 in the next column.

    Similarly if 2 values are selected then, 2 and so on..

    As can be seen in the image below, if two options in impacted pillar column are checked impacted points should be 2.

     

    IF.png

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try...

     

    =COUNTM([Impacted Pillar]@row)

    thinkspi.com

  • It worked! I did't know something like COUNTM exists!!

    Thank you :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. It has only been around for about a month. Previously you would have needed to adapt one of the solutions using the LEN and CHAR(10) functions.

    thinkspi.com

  • alcapps
    alcapps ✭✭✭✭✭✭

    I am trying to write a formula that will answer Yes if a request has been approved but only if "Patient Report" is selected in a multiselect field. If "Patient Report" is not selected, there is no need for approval. This is part of a larger formula with several other fields involved that answers Yes if all criteria has been met. When I add this piece (does not contain), the formula does not work anymore.

    If [multiselect cell] contains "specific text" and [approval cell] is not blank or [multiselect cell] does not contain "specific text" then yes otherwise no?

    I've tried this but it doesn't seem to be working...

    =IF(OR(AND(CONTAINS("Patient Report", [Systems Affected]),[Medical Director Approval]@row <>""), NOT(CONTAINS("Patient Report",[Systems Affected]@row))), "Yes", "No")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I follow.

     

    Your formula says to populate "Yes" if

     

    [Systems Affected] contains "Patient Report" and [Medical Director] is not blank

    or

    [Systems Affected] does not contain "Patient Report"

    .

    If you have confirmed this is how you want the formula to work, then the only issue would be a row reference in your first CONTAINS function.

     

    You have:

    =IF(................CONTAINS("Patient Report", [Systems Affected])...................)

    but it should be 

    =IF(................CONTAINS("Patient Report", [Systems Affected]@row)...................)

    thinkspi.com

  • alcapps
    alcapps ✭✭✭✭✭✭

    Wow! That's what it was. :) 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Jennifer Lenander
    Jennifer Lenander ✭✭✭✭✭✭

    I have a column that has about 20 dropdown options and multiple options can be selected. On a separate sheet I have all 20 options listed and I want to know how many times each specific option was selected in my main sheet. I have tried various formulas and nothing is working. Does anyone have any ideas?


    =COUNTIF({Range 1}, [Specific Claim]1)

    =COUNTIFS({Range 1}, CONTAINS([Specific Claim]1, {Range 1}))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Jennifer Lenander

    Try using Paul's formula at the top of this thread:

    =COUNTFS({Range 1}, CONTAINS([Specific Claim]1@cell))


    Since it's a multi-dropdown column, you may want to try the HAS function instead:

    =COUNTF({Range 1}, HAS(@cell, [Specific Claim]@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!