Need Help with NOT CONTAINS

leslie.mislavillalba

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.

Paul Newcome

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")

leslie.mislavillalba

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).

Paul Newcome

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")