COUNTIF

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

Find more posts tagged with

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…