How do I create an CountIF statement to include multiple selections within a cell?

I'm creating sheet summary calculations to tally the total count of a specific dropdown menu item; however, I am using multi-select. I know how to use a standard COUNTIF formula, but it only returns the total count if the option is the only one selected. Here is the formula I'm using:

=COUNTIF([Level III Request]:[Level III Request], "Solution Video")

The option for Solution Video appears six times but only three times by itself, so the formula returns a total count of 3. What logic is required to count the option when bundled with other selections? Below is a screenshot of my sheet. Any help is greatly appreciated!


Tags:

Best Answers

  • gstotts
    gstotts Employee
    edited 10/13/23 Answer ✓

    Try using a CONTAINS() statement in your criteria for the COUNTIF:

    =COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hey @Jeannette Leary

    Can you post the formulas you've tried?

    In any of your formulas (e.g. COUNTIFS) you'll need to make sure you're also using HAS to see if the cell has that value selected (versus = that exact value without other selections).

    For example:

    =COUNTIFS({column range}, HAS(@cell, "Value"))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!