Counting identical words in a column
Hello everyone,
Please I need help with a formula that helps me count how many times the word "diabetes" is repeated in a column called "MRA ACTIVE CODES" (1727 rows). I have tried the following formula, but it doesn't work, it gives me 0.
=COUNTIF([MRA ACTIVES CODES1:MRA ACTIVES CODES1727], "*diabetes*")
Thanks in advance, I'm new to formulas and these Smartsheet things, I appreciate any help you can give me.
Answers
-
Hey @VirginiaLazor
Try this
=(LEN(JOIN([MRA ACTIVES CODES]:[MRA ACTIVES CODES])) - LEN(SUBSTITUTE(JOIN([MRA ACTIVES CODES]:[MRA ACTIVES CODES]), "diabetes", ""))) / LEN("diabetes")
Will this work for you?
Kelly
-
@Kelly Moore It doesn't work unfortunately, it gives me a number that is not :(
But thanks anyway for trying to help me🙌🏻
-
Hey @VirginiaLazor
The above formula looked specifically for the word 'diabetes' and should have correctly counted all instances of that textstring. What number were you expecting, and what value was returned? Note that the formula above also used the entire column and not exactly rows 1-1727. If it must be only those rows and nothing more, change the column designation to your absolute row references. Also note that absolute references create a static reference, that is, the range will not grow as more rows are added. The column range in the formula I provided will dynamically grow.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!