countIF a value column if the attribute is along side another attribute in a "Multi select Column"

Options

Does anybody know if you can create a formula that will count the values in a “dropdown (Multi-Select) column", when adding summary fields on a Smartsheet list?

What is happening now is, if I have Apple and Orange in one field and I want a count for Apple. it will not count it. Only if I have Apple along in a field?

If I am counting how many time the word apple is in a column I want it to count it if it is alone or if it is along side another selection in that some cell.

Tags:

Best Answer

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭
    Answer ✓
    Options

    You can use the CONTAINS function in your formula to get a count. Example:

    =COUNTIFS([Column1]:[Column1], CONTAINS("Apple", @cell))

    That would give you the total number of cells that have Apple in it. If you want a distinct count for when Apple is alone use this:

    =COUNTIFS([Column1]:[Column1], "Apple")

    If you want a count of Apple + something else, this would work but it is cumbersome if you have a lot of potential pairings:

    =COUNTIFS([Column1]:[Column1], AND(CONTAINS("Apple", @cell), CONTAINS("Orange", @cell))

Answers

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭
    Answer ✓
    Options

    You can use the CONTAINS function in your formula to get a count. Example:

    =COUNTIFS([Column1]:[Column1], CONTAINS("Apple", @cell))

    That would give you the total number of cells that have Apple in it. If you want a distinct count for when Apple is alone use this:

    =COUNTIFS([Column1]:[Column1], "Apple")

    If you want a count of Apple + something else, this would work but it is cumbersome if you have a lot of potential pairings:

    =COUNTIFS([Column1]:[Column1], AND(CONTAINS("Apple", @cell), CONTAINS("Orange", @cell))

  • Thank you, that did the trick! :-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!