count rows that contains certain texts in two different columns

Shinya

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

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