Multi-Select Count

Options

I have a Smartsheet with a multi-select column. I am trying to determine the formula that will allow me to count how many rows have 1 item selected and how many rows have more than 1 item selected. The specific item is irrelevant. I am just looking for how many equal 1 item selected and how many are greater than 1 item selected. Any suggestions would be greatly appreciated.

Answers

  • Brandon R.
    Brandon R. Employee
    Options

    The COUNTIFS formula should work for you since you're looking for more than one value.

    =COUNTIFS(range1,HAS(@cell,”searchvalue”),range2,value2)) - Formula for counting values in a column that has more than one value.

    For the single value, a simple COUNT formula should do the trick as well.

    =COUNT([Units Sold]:[Units Sold])

    I would also incorporate an ISBLANK or IFERROR as well into the formula so that rows that don't match any value don't throw up a error message.

    "Insanity is doing the same thing over and over again, and expecting a different result" - Albert Einstein

    “A wise man can learn more from a foolish question than a fool can learn from a wise answer.” - Bruce Lee

  • Burrsie76
    Options

    Thanks Brandon. I got the single value count to work, but I am still struggling with the 2 or more values.

  • Brandon R.
    Brandon R. Employee
    Options

    So this one will work if both boxes are marked. What we would still need to figure out is how to add the function in for when only one or the other are marked.

    =IF([GET/offer-detail]@row, [Value 1]@row, 0) + IF([GET/offer-details]@row, [Value 2]@row, 0).

    If you need to add more checkboxes, you would just do something like this where you would add another IF statement along with the + because you're adding the three together. But this will only work when all boxes are marked. So we jsut need to figure out the single value.

    =IF([GET/offer-detail]@row, [Value 1]@row, 0) + IF([GET/offer-details]@row, [Value 2]@row, 0) + IF([Column 3]@row, [Value 3]@row, 0).

    "Insanity is doing the same thing over and over again, and expecting a different result" - Albert Einstein

    “A wise man can learn more from a foolish question than a fool can learn from a wise answer.” - Bruce Lee

  • Burrsie76
    Options

    So these are the drop down. I am not concerned about knowing which specific item is selected. I just need to know whether 2 or more were selected (regardless of which one).

  • Brandon R.
    Brandon R. Employee
    Options

    For this one, I will say send in a support ticket and we can test this further. Since we're wanting to use multi-select dropdown column we would need to figure out the best way to create this. Make sure to include the examples we gave you already so that they know what we built so far and can expand on that further.

    "Insanity is doing the same thing over and over again, and expecting a different result" - Albert Einstein

    “A wise man can learn more from a foolish question than a fool can learn from a wise answer.” - Bruce Lee

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!