COUNTIF

Chaz P.

Hello and good morning Smartsheet community,

I know you can use the =COUNTIF in order to count the number of times a (SINGLE) name or word appears in a range of cells. My issue is that I am trying to count how many times the word safety is used in a range of cells where the word safety is used in a sentence and not the only word in the cell. Is this possible and if so, I would truly appreciate any help. I hope everyone has a spectacular day!

Regards-

Chaz

Mike Wilday

Yes! This is possible if you use a FIND in the query as well. 

=COUNTIFS([Column Name]:[Column Name], FIND("Safety", @cell) > 0)

This will count every instance where the word safety is found in the range. Just replace both instances of "Column Name" with your column name. 

Find returns the numerical position where Safety is found, it will give you a number that is greater than 0 if it finds the word safety it will return a number and if it's greater than 0 then it will count that cell. 

L_123

=COUNTIF(A:A, FIND("safety", @cell) > 0)

J. Craig Williams

Be aware that

FIND is case-sensitive:

=COUNTIFS(A:A, FIND("search-term", LOWER(@cell)) > 0)

If the search term appears more than once in a single cell, that count remains at 1 for that cell.

And there is no reason to use COUNTIF instead of COUNTIFS.

Craig

 

 

Mike Wilday

Aw yes, that dreaded case sensitive. Here is the revision to my original. It's important to note that Smartsheets doesn't use column letters like Google Sheets and Excel. You need to replace the column letters with the actual column name of the column you are searching the term Safety in. I added the lower to the at cell and added the word "safety" in lower case to compensate. 

=COUNTIFS([Column Name]:[Column Name], FIND("safety", LOWER(@cell)) > 0)

Hope that works for you. 

Chaz P.

Good morning all,

I would like to thank each of you for your support, certainly appreciated. I'll certainly be putting this formula to use. I hope everyone has a fantastic day and thank you all again. This community is certainly lucky to not only have the knowledge but having people, such as you all, who are willing to share their knowledge is remarkable!

Wishing everyone a great day!

 

Regards-

Chaz

  

Mike Wilday

Glad we could be of assistance Chaz. Enjoy Smartsheets.

J. Craig Williams

Thank you for the acknowledgement. 

Craig