Formula to count the number of instances in a column from a multi-entry 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 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?
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 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))
-
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.
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 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))
-
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 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!