COUNTIF and CONTAINS in a formula.

Hello! I am trying to count the number of times where a specific phrase is.

For example, I have XX - YYYYY as my specific phrase; but some times there might be an XX - YYYYY - ZZZZ or even UUU - XX - YYYYY - ZZZZ.

Is there anyway to count the amount of times that "XX - YYYYY" exists?

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Cantine

    Try this. You will need to replace the column name with your exact column name and your placeholder XX-YYYY.

    =COUNTIFS([your column]:[your column], CONTAINS("XX-YYYY", @cell))

    If your data is on a different sheet, the format changes for cross-sheet references.

    =COUNTIFS({your source sheet column}, CONTAINS("XX-YYYY", @cell))

    Will this work for you?

    Kelly

  • Cantine
    Cantine ✭✭

    Hi @Kelly Moore !

    Can a @cell be an entire column?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Cantine

    One uses the @cell to tell smartsheet to evaluate every cell within a range (ie a column).

    In this example

    =COUNTIFS([your column]:[your column], CONTAINS("XX-YYYY", @cell))

    The range that smartsheet will look in for the Contains is the entire column [Your column]. It then says to see if each individual cell (@cell) in that range contains 'XX-YYYY' and count it if it does. Is this what you are asking? Or are you asking how to make what it is searching for dynamic, ie, you don't want to hard code in something like XX-YYYY?

    If you can share a screenshot or a generic mockup, it is always super helpful to the community. Don't share any sensitive info

    Kelly

  • Hi Kelly, How do I count the number of YES responses within a row?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @RT2

    Try something like this:

    =COUNTIF([First Column cell]@row:[Last Column cell]@row, "YES")

    You will need to replace the column names with whatever that first column is titled (MONDAY ON VACATION?) and the last column is titled (SUNDAY ON-VACATION?)

    The column title needs to be letter-for-letter the same. If this hasn't helped, it would be useful to see the formula that you tried and what your column names are 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!