Count Multiple items within a single cell - Countif ?
Answers
-
Hello Team,
I hope you are doing great!
I have a similar question. In the scenario, I am trying to resolve, I have a single cell with multiple values, where I would like to count the occurrence of specific word.
=COUNTIFS([Defect Category]@row; CONTAINS("Decision"; @cell))
Current status:
- Formula provides me with 1 as count of "Decision" in the cell
Desired outcome
- It should show actual count, even if value is duplicated. In our case column Decision Error shall be "2"
Could you please advise?
Thanks,
Roman
-
The formula is checking to see if the cell contains that value, and if it does (once, twice, three times) then the answer is "yes!" or 1, which is why you see 1 instead of 2. It is unable to count the cell for multiple instances of the same word.
I don't believe there's a way to do this based on your current set-up, but I'd love to be proved wrong if someone else can come up with a clever solution!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Romano el Polako We just did this in another thread last week I believe.
Basically you remove the desired word from the string entirely and subtract that character count from the original character count. Dividing that by the number of character in a single instance of that word should give you how many times that word appears in a cell.
=(LEN([Defect Category]@row) - LEN(SUBSTITUTE([Defect Category]@row, "Decision", ""))) / LEN("Decision")
Using the circled row in your screenshot, that gives us
=(31 - 15) / 8
=(16) / 8
=2
-
Hey!
Thank you Paul, solution works, outstanding!
Thanks,
Roman
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!