Formula to count the number of instances in a column from a multi-entry cell

Pamela Wagner

(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 multi-entry 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?

Kelly Moore

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 exact-match function which is exactly why it can work in multi-select 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))

https://help.smartsheet.com/function/contains

https://help.smartsheet.com/function/upper

Kelly Moore

Hey Pamela

CONTAINS is one of only a few functions that work in a multi-select 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.

Kelly Moore

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 exact-match function which is exactly why it can work in multi-select 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))

https://help.smartsheet.com/function/contains

https://help.smartsheet.com/function/upper

Pamela Wagner

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!

Kelly Moore

Hey Pamela

CONTAINS is one of only a few functions that work in a multi-select 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.