count rows that contains certain texts in two different columns

Hi Smartsheet,

I am struggling with a formula that can solve the following issue:

A smartsheet contains multiple columns, two of them (A and B) are texts. I want to count the total number of rows that column A's text contains a text string "XX" OR column B's text contains a text string "YY". Please note, when on a certain row, if column A has "XX" and column B has "YY", it counts as one. In other words, on a certain row, as long as at column A or B has either "XX" or "YY", it will count as one and only one.

I tried "CONTAINS", but it seems not working.

Please help!

Thanks,

Sean

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Shinya

    Hope you are fine, please use the following formula:

    =COUNTIFS(A:A, CONTAINS("XX", @cell)) + COUNTIFS(B:B, CONTAINS("YY", @cell)) - COUNTIFS(A:A, CONTAINS("XX", @cell), B:B, CONTAINS("YY", @cell))

    the following screenshot show the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!