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 !

Find more posts tagged with

Formulas

functionality

Accepted answers

L_123

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

All comments

L_123

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.