COUNTIF

Chaz P.
Chaz P.
edited 12/09/19 in Formulas and Functions

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

Comments

  • Mike Wilday
    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
    L_123 ✭✭✭✭✭✭

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

  • J. Craig Williams
    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
    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. 

  • 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
    Mike Wilday ✭✭✭✭✭✭

    Glad we could be of assistance Chaz. Enjoy Smartsheets.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Thank you for the acknowledgement. 

    Craig

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!