Counting values from a multiple selection drop down list

[JoanneIE]

Hi,

I am trying to count how many times a value (a word) appears in a column, from a multiple dropdown list.

In the above screenshot, "Neck" appears three times. If I use =COUNTIF(discomfort:discomfort), "Neck") I get 0 as a result. If I use =COUNTIF(discomfort:discomfort),"Wrists") I get 1 as a result - which you can see and is correct, but only appears to work because it is the only option on that line.

I am not very excel literate, but I can follow instructions (mostly) and I have read lots of forum posts re HAS, COUNTM and other syntaxes, but I still cannot work this one out myself.

Any help greatly appreciated. Thank you.

[#2]

@JoanneIE 

=COUNTIF(discomfort,"*neck*") would work, provided that the list of discomforts is in a named range called 'discomfort'.

The asterisks are wild-cards and tell Excel to look for anything with the text "neck" in it. Not necessarily and exact match for the whole text in a cell. That's why "wrists" worked as it had no other text with it.

[#3 · Accepted solution]

Kindly use =COUNTIF(discomfort:discomfort), "*Neck*") and you will get the result.

Note - The asterisks (*) act as wildcards, allowing for any characters before or after "Neck" in the cell.

[#4]

Thank you so much. This has worked 🙂