COUNTIF and CONTAINS in a formula.

Cantine

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?

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

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

Hi @Kelly Moore !

Can a @cell be an entire column?

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

RT2

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

Genevieve P.

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 🙂

RT2

Thank you Genevieve that worked