Formula to count the number of instances in a column from a multientry cell
(see edit at end of discussion)
I have a survey that will allow people to choose what they would like to hear about in a workshop. The column is multientry so they can put from 1  10 choices. I would like to create a report that pulls in the number of times each item is chosen. A regular countIF statement that I'm using isn't working since the cell holds more than one item. What formula or set up, will allow me to see, from the 10 options, how often they occur?
The formula currently in use: =COUNTIF({List}, "Creating a charter")
I would also like to streamline this formula so I don't have to type in the name of the criteria but rather that it look at the cell to the left to see what the criteria is. Here is what the table looks like:
Here is the master sheet:
Edit: I got this to work using an existing formula from another sheet:
=COUNTIFS({List}, CONTAINS(UPPER([Task Name]@row), UPPER(@cell)))
However, I don't understand the use of CONTAINS and UPPER  can any one explain these uses?
Best Answers

Hey @Pamela Wagner
Glad you got your formula to work.
The CONTAINS function looks at a text range  in your case your cell, to see if the range 'contains' whatever you're searching for. For example, if your text was ABC111xyz and you asked does this cell contain '111' the answer would be true. If you were just looking for '111' and the text was 111111111, it would still give a true response. CONTAINS is not an exactmatch function which is exactly why it can work in multiselect dropdown cells.
The UPPER function is used typically when manual entries are allowed and you don't know if the responses will be upper case, lower case or maybe a mixture. Most functions are looking for an exact match. The UPPER function forces the text to UPPER case. In your current formula is doesn't look like it's necessary as the responses come from a drop down list, not a manual entry.
=COUNTIFS({List}, CONTAINS([Task Name]@row, @cell))

Hey Pamela
CONTAINS is one of only a few functions that work in a multiselect cell. Another is HAS  it is an exact match function.
As you noticed in your original formula, without CONTAINS the formula only saw the phrase if it happened to be the first or only phrase in the cell. In this case it was definitely needed.
Answers

Hey @Pamela Wagner
Glad you got your formula to work.
The CONTAINS function looks at a text range  in your case your cell, to see if the range 'contains' whatever you're searching for. For example, if your text was ABC111xyz and you asked does this cell contain '111' the answer would be true. If you were just looking for '111' and the text was 111111111, it would still give a true response. CONTAINS is not an exactmatch function which is exactly why it can work in multiselect dropdown cells.
The UPPER function is used typically when manual entries are allowed and you don't know if the responses will be upper case, lower case or maybe a mixture. Most functions are looking for an exact match. The UPPER function forces the text to UPPER case. In your current formula is doesn't look like it's necessary as the responses come from a drop down list, not a manual entry.
=COUNTIFS({List}, CONTAINS([Task Name]@row, @cell))

Thanks @KDM! Would you suggest another formula in that the user of UPPER is not really needed? I'm thinking that neither CONTAINS and UPPER are needed but they worked in a pinch!

Hey Pamela
CONTAINS is one of only a few functions that work in a multiselect cell. Another is HAS  it is an exact match function.
As you noticed in your original formula, without CONTAINS the formula only saw the phrase if it happened to be the first or only phrase in the cell. In this case it was definitely needed.
Help Article Resources
Categories
Check out the Formula Handbook template!