CountIF Has Multi Select Count

Options

Hi all - I know this question has been asked many times and I've tried all of those solutions, but my output keeps showing up as zero…. I'm going to provide super specifics as I'm not sure if it's an issue with how my column is configured - I can't seem to figure it out!

Column "Goals" is a dropdown list with 5 options and multi-select (restricted to list values only). The selections are a little bit wordy, so not sure if this would factor.

I need to count every time a certain option appears in the column (obviously including the ones that have multiple answers)

Current formula I'm using based on other answers in this forum:

=COUNTIF({goals}, HAS({cellname}, "Doesn't Matter, Get Better"))

goals = entire column

cellname = I clicked on one cell within the column "goals"

I saw other answers referencing @cell. I was not entirely sure what this meant so I assumed its clicking on an individual cell within the column I want to search for.

I can confirm using a standard COUNTIF formula works great, it obviously just doesn't count the ones that have multiple answers.

HELP PLEASE! :)

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @ajk1317,

    The @cell is what you would want to use here:

    =COUNTIF({goals}, HAS(@cell, "Doesn't Matter, Get Better"))

    This would count the cells in the Goals column where a cell has "Doesn't Matter, Get Better" as a selected option.

    Hope this helps, but if you have any problems/questions then let us know!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    @ajk1317, here you would switch from using COUNTIF to using COUNTIFS as you have multiple criteria. The formula would be very similar, just with an added S (bolded):

    =COUNTIFS({goals}, HAS(@cell, "Doesn't Matter, Get Better"), {projectstate}, "Active")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!