SOLVED

Counting values from a multiple selection drop down list

Copper Contributor

Hi,

 

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

JoanneIE_0-1704970713830.png

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.

 

 

3 Replies

@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.

best response confirmed by JoanneIE (Copper Contributor)
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.
Thank you so much. This has worked :)
1 best response

Accepted Solutions
best response confirmed by JoanneIE (Copper Contributor)
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.

View solution in original post