Count partial text


I would like to count the appearance of partial text out of a bigger text.

e.g - Find 2020-04 form text that may contain 2020-04, 2020-05

Tried using FIND but probably the wrong way


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you trying to find where within the string the text falls, or are you trying to search multiple cells and count how many contain that text?

    To locate within the string:

    =FIND("2020-04", [Column Name]@row)

    To count how many across a range:

    =COUNTIFS([Column Name]:[Column Name], CONTAINS("2020-04", @cell))

  • I have done the following:

    =COUNTIFS([Fix Versions]:[Fix Versions], CONTAINS("2020-04", [Fix Versions]14:[Fix Versions]30))

    I got 0

    Probably I used @cell wrongly. What should I do?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Actually use "@cell" (without the quotes).

    The @cell reference tells the function to look across the previously specified range and evaluate each cell individually.

    =COUNTIFS([Fix Versions]:[Fix Versions], CONTAINS("2020-04", @cell))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!