# Need Help with NOT CONTAINS

Options

Hello, I need help with a formula using NOT CONTAINS

This is my current formula, which is giving me an incorrect count.

=COUNTIFS({Another Sheet Column 1}, CONTAINS("X", @cell), {Another Sheet Column 2}, "1", {Another Sheet Column 1}, NOT(CONTAINS("B", @cell)), {Another Sheet Column1} NOT(CONTAINS("C", @cell)))

Values of "B", "C" and "X" belongs to the same column, listed as "Another Sheet Column 1"= all three values can be assigned to one cell. (allow multiple values per cell)

Checkbox column (1=checked box) = "Another Sheet Column 2"

I need the formula to count all rows that contains both, "X" (Column1) and "1" (Column2) ONLY, excluding all rows that have both "1" (Column 2) and "X", "B" and "C" listed under the same cell in (Column 1).

Hope this help! Thank You.

• ✭✭✭✭✭✭
Options

If you want it to be 1 and ONLY "X", you can get rid of the NOT/CONTAINS combo and just count on cells that are equal to "X".

=COUNTIFS({Another Sheet Column 1}, @cell = "X", {Another Sheet Column 2}, @cell = "1")

• Options

Thank You Paul for your input, but still not working. Now is giving me a value of "0", it should be "3". Column 1 can have "X" plus any other value other than "B" or "C", this is why I choose to add CONTAINS("X", @cell) and NOT/(CONTAINS).

• ✭✭✭✭✭✭
Options

So "XA" is fine but "XAB" is not fine because it contains the "B"?

=COUNTIFS({Another Sheet Column 1}, AND(CONTAINS("X", @cell), NOT(CONTAINS("B", @cell)), NOT(CONTAINS("C", @cell))), {Another Sheet Column 2}, @cell = "1")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!