IF function with OR to return a value of 0 or 1

Hi,

I'm trying to set a value of 1 or 0 in a field depending on the value in a field called Communication Language. The field can have any or all of these values. I keep getting an #UNPARSEABLE error and I can't figure out what it doesn't like. I want it to have a value of 1 if it is any language other than these.

=IF([Communication Language]@row ="French" , "1",

IF [Communication Language]@row ="German", "1",

IF([Communication Language]@row = "Spanish", "1",

IF([Communication Language]@row = "Korean", "1",

IF([Communication Language]@row = "Japanese", "1",

IF([Communication Language]@row = "Chinese (Simplified)", "1",

IF([Communication Language]@row = "Chinese (Traditional)", "1", "0")

It is probably obvious, but I can't see the issue. Maybe I have looked at it for too long.

Thanks for your help!

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @sackerman70

With a multi-select cell, if you search for = "value" then it will only be true if that one value is in the cell. That means that to search for English, you can use the same structure as your current formula but change the output from being 1 to being the text you want, like so:

=IF([Communication Language]@row = "English", "English Only",

Then you can look to see if that cell HAS English along with anything else, and use that to say "translated":

IF(HAS([Communication Language]@row, "English"), "Translated",

Since the formula will only move on to the second statement if the first statement is FALSE (or in this case, if the cell is not only "English"), then you don't have to write anything else in the logic. For your final statement, you could return something that shows none of the selections are English... perhaps:

=IF([Communication Language]@row = "English", "English Only", IF(HAS([Communication Language]@row, "English"), "Translated", "No English"))

If I've misunderstood and you're only looking for "english only" and "all other possible selections", then you can simplify it by only having the first statement and the alternative text:

=IF([Communication Language]@row = "English", "English Only", "Translated")

Cheers,

Genevieve

All comments

Andrée Starå

Hi @sackerman70

I hope you're well and safe!

Try something like this.

=IF([Communication Language]@row =French , 1,

IF [Communication Language]@row =German, 1,

IF([Communication Language]@row = Spanish, 1,

IF([Communication Language]@row = Korean, 1,

IF([Communication Language]@row = Japanese, 1,

IF([Communication Language]@row = Chinese (Simplified), 1,

IF([Communication Language]@row = Chinese (Traditional), 1, 0)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

sackerman70

Thank you Andree! I am still getting the unparseable error. Does it matter that the field I am reference for Communication Language is a drop down field type? I noticed when I put in the formula that some pieces are red, but I wasn't able to find any information on why that would be. I thought maybe it was trying to show me what it doesn't like. Everything is spelled right. I tried putting " around the language values to see if that was the issue, but still got the error.

sackerman70

I was able to get my first formula to work finally. I used the following:

=IF([Communication Language]@row = "French", 1, IF([Communication Language]@row = "German", 1, IF([Communication Language]@row = "Spanish", 1, IF([Communication Language]@row = "Korean", 1, IF([Communication Language]@row = "Japanese", 1, IF([Communication Language]@row = "Chinese (Simplified)", 1, IF([Communication Language]@row = "Chinese (Traditional)", 1, 0)))))))

However, now realize that it isn't doing what I actually want. The Communication Language field is a drop down list that allows multiple values in the cell. So a cell could have English, French or English, French, Spanish, Japanese, Korean, German or any combination of the 8 languages. I have tried introducing an OR within the IF statement, but it just breaks it the formula and I get an error. Is it possible in Smartsheet to use an IF statement like this?

The ideal scenario would be to have a result of "English Only" if the only value is English and then if there is more than English have a result of "Translated".

Genevieve P.

Hi @sackerman70

=IF([Communication Language]@row = "English", "English Only",

Then you can look to see if that cell HAS English along with anything else, and use that to say "translated":

IF(HAS([Communication Language]@row, "English"), "Translated",

=IF([Communication Language]@row = "English", "English Only", IF(HAS([Communication Language]@row, "English"), "Translated", "No English"))

If I've misunderstood and you're only looking for "english only" and "all other possible selections", then you can simplify it by only having the first statement and the alternative text:

=IF([Communication Language]@row = "English", "English Only", "Translated")

Cheers,

Genevieve

Paul Newcome

Some things to watch out for based on your last screenshot:

You definitely need the quotes around the languages (or any other text string in a formula).

The space after the second IF should be removed.

IF (

IF(

sackerman70

Thank you for your help everyone! Your suggestion was exactly what I needed Genevieve.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.