Counting identical words in a column

VirginiaLazor

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.

Kelly Moore

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

VirginiaLazor

@Kelly Moore It doesn't work unfortunately, it gives me a number that is not :(

But thanks anyway for trying to help me🙌🏻

Kelly Moore

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.