CountIF Has Multi Select Count

ajk1317

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! :)

Nick Korna

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

@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")

Nick Korna

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!

ajk1317

https://community.smartsheet.com/discussion/comment/433642#Comment_433642

@Nick Korna This was super helpful and worked. I didn't know it was literally type "@cell". Thank you!

Would you mind helping one more function into the same formula so I add a perimeter. I need the same function to weed out the options that only show "Active" as an option in a different column.

Formula I have it as is:

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

Hoping my question makes sense. Not sure if I have to add an "And" function to this? Thank you in advance

Nick Korna

@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")

ajk1317

https://community.smartsheet.com/discussion/comment/433910#Comment_433910

Much appreciated! Thank you @Nick Korna