Need help master complex IF statement to assign multiple values to multiple people

[Marc_Maurer]

Hi there,

I have an IF clause complex that unfortunately stretches my abilities beyond the possible. Here is the challenge:

I have a column with a variety of about 15 industries, the column name is {Primary Industry} and examples include Healthcare, Justice, Public Sector, Dealerships, Fitness, Utilities, and Hospitality (there are more than 7 but lets focus on these four for the purpose of my problem).

I now have a new column called {Account ownership}. In this column I like to assign the ownership for the different accounts according to the Primary Industry they belong. I.e. all accounts with a Primary Industry “Dealerships” and “Healthcare” belong to Sven, those with “Justice” and “Public Sector” to Toni, and those with “Utilities”, “Hospitality”, and “Fitness” to Sandy.

This is how I tried to solve it:

IF(
{Primary Industry}NOT(),
IF(
{Primary Industry}=‘Dealerships’),
“Sven”,
IF(
{Primary Industry}=‘Healthcare’),
“Sven”,
IF(
{Primary Industry}=‘Justice’),
“Toni”
IF(
{Primary Industry}=‘Public Sector’),
“Toni”
IF(
{Primary Industry}=‘Utilities’),
“Sandy”
IF(
{Primary Industry}=‘Hospitality’),
“Sandy”
IF(
{Primary Industry}=‘Fitness’),
“Sandy”
)
)
)
)
)
)
)

I get the error message “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” I don’t think that I missed it on the number of brackets at the end as I tried to add and remove some.

Any ideas to master this complexity?

Thanks A TON!!!

Marc

[JonathanBowen]

Hi @Marc_Maurer - I think you’ll find a SWITCH() formula easier for this scenario:

SWITCH(
  {Primary Industry}, 
  'Dealerships', 'Sven', 
  'Healthcare', 'Sven', 
  'Justice', 'Toni'
) 

This evaluates the Primary Industry field and:
If Dealerships, set to Sven
If Justice, set to Toni
and so on.

JB

[Justin_Barrett]

Totally agree with @JonathanBowen re: using SWITCH over a bevy of nested IFs. For future reference, though, here are the issues I see with your existing IF structure.

First off, the empty NOT() is probably one reason for the “Invalid formula” message. Not only is its emptiness likely a problem, but its placement adjacent to {Primary Industry} is definitely a problem In other words, that’s not how to use NOT(). What NOT() does is take the comparison you put inside it and invert its result. So if the comparison inside NOT() returns true, NOT() will return false, and vice versa.

Were you trying to only run the rest of the comparisons if {Primary Industry} is not empty? If so, then that’s not the way to go about it. Remove the NOT() completely, and leave {Primary Industry} there on its own. That effectively checks to see if {Primary Industry} contains anything, and only continues to the rest if that’s true.

Your outer IF structure is fine, but the inner ones are not formatted correctly. The structure should be:

IF(comparison, result-if-true, optional-result-if-false)

You put the closing parenthesis of each of the inner IFs following the comparison, which is another reason for the failure.

You’re also missing commas after some of your name strings—after “Toni” and the first two instances of “Sandy”—and one more closing parenthesis at the end of the whole thing.

With these items corrected, it should look like this (indented for further clarity):

IF(
    {Primary Industry},
    IF(
        {Primary Industry}='Dealerships',
        “Sven”,
        IF(
            {Primary Industry}='Healthcare',
            “Sven”,
            IF(
                {Primary Industry}='Justice',
                “Toni”,
                IF(
                    {Primary Industry}='Public Sector',
                    “Toni”,
                    IF(
                        {Primary Industry}='Utilities',
                        “Sandy”,
                        IF(
                            {Primary Industry}='Hospitality',
                            “Sandy”,
                            IF(
                                {Primary Industry}='Fitness',
                                “Sandy”
                            )
                        )
                    )
                )
            )
        )
    )
)

…but as I said at the top, @JonathanBowen is right. SWITCH is so much easier for stuff like this. Whenever you’re checking a single item for one of many possible options, and changing the output based on what you find, go with SWITCH.

[Marc_Maurer]

Hi Jonathan,

Awesome, it works like a charm and it is much more simple! this is super!!!

All the best,

Marc