Need help with an "IF", "CONTAINS" and "OR" formula

TReid · 05/31/20

Hello everyone

First, thanks for your help :) I'm trying to make a formula. I have a cell (cell#1) that has 4 values in it (" one", "two", "three", "four").

I'm looking for a formula that would work like this:

If cell #1 contains "one" or "two", then cell #1 =0. Otherwise, calculate the number of values in cell #1.
Could also be : calculate the number of values in cell #1, EXCEPT values "one" and "two".
the values are currently chosen from a dropdown multi select list

Merci !

L_123 · 06/01/20

If cell1 contains one or two then return 0 -> =IF(OR(CONTAINS("one", A1), CONTAINS("Two", A1)), 0, COUNTM(A1))

If countif not one or two -> =COUNTM(A1) - (IF(CONTAINS("one", A1), 1, 0) + IF(CONTAINS("two", A1), 1, 0))

L_123 · 06/01/20

If cell1 contains one or two then return 0 -> =IF(OR(CONTAINS("one", A1), CONTAINS("Two", A1)), 0, COUNTM(A1))

If countif not one or two -> =COUNTM(A1) - (IF(CONTAINS("one", A1), 1, 0) + IF(CONTAINS("two", A1), 1, 0))

TReid · 06/01/20

Thank you so much ! I tried many times but there was always a mistake I couldn't figure out ! The two formulas works perfectly ! Merci

Best Answer