Need help with an "IF", "CONTAINS" and "OR" formula
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 !
Best Answer
-
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))
Answers
-
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))
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!