Multiple If statements

Heather Simmons

I am struggling with a multiple if statement....I need to have 8 total. Below is just a start with two. They each work individually (in separate cells) but when I put them together I receive the UNPARSEABLE error. Anyone that can help me get this to work?

=IF(OR([Place in Funnel]2 = "Universe", [Place in Funnel]2 = "Above"), "0%") , (AND([Place in Funnel]2 = "In", [Sales Stage]2 = "Lead"), "10%")

Genevieve P.

Hi @Heather Simmons

I think what Mark was pointing out is the way that you combined the two IF statements needed to be adjusted, the Syntax.

In your first example, you can "closed off" the first IF statement with a closing parentheses. To add IFs together, you'll need to keep the closing parentheses to the very very end of the entire statement (2 or 8 IFs down the line, depending on how long your statement is).

You also need to repeat the IF function itself, after a comma, which you left out of your first example as well.

IF on its own:

=IF(logic statement, then value if true, then value if false)

=IF(OR([Place in Funnel]2 = "Universe", [Place in Funnel]2 = "Above"), "0%")

IF added with another IF

=IF(logic statement, then value if true, IF(Logic statement, then value if true, then value if false)) <two closing parentheses

=IF(OR([Place in Funnel]2 = "Universe", [Place in Funnel]2 = "Above"), "0%" , IF(AND([Place in Funnel]2 = "In", [Sales Stage]2 = "Lead"), "10%"))

So if you had 8 IFs, at the end of your statement you would have 8 closing parentheses. Does that make sense? Should you have each of the 8 statements separated, we'd be happy to help you put them all together and get the correct syntax.

You may also want to take a look in the Help Center (see here), or watch some of the webinars on formulas (see here).

Cheers,

Genevieve

Genevieve P.

Hi @Heather Simmons

My apologies! I closed off the last IF statement but there was no AND as a part of it, so that closing parentheses needs to go at the very end along with all the others.

Try this:

=IF(OR([Place in Funnel]@row = "Universe", [Place in Funnel]@row = "Above"), 0 , IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Lead"), 0.1, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Opportunity"), 0.2, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Qualifying"), 0.5, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Negotiation"), 0.7, IF(AND([Place in Funnel]@row = "In", OR([Sales Stage]@row = "Proposal – Individual for Job", [Sales Stage]@row ="MSA", [Sales Stage]@row ="Agreement")), 0.8, IF([Place in Funnel]@row= "Closed",1))))))))

Mark Cronk

Hi Heather, To nest IF statements you need to end an IF with another IF until you're done.

=IF(OR([Place in Funnel]2 = "Universe", [Place in Funnel]2 = "Above"), "0%" , IF(AND([Place in Funnel]2 = "In", [Sales Stage]2 = "Lead"), "10%", IF(.....

Work?

Heather Simmons

Thanks Mark,

The multiple IF statement I posted....just that by itself does not work....wanted to make sure just two would work together before I go through the effort of adding all eight. When I separate the two I have and use them individually, it works, but when I put them together, it does not work.

Genevieve P.

Hi @Heather Simmons

I think what Mark was pointing out is the way that you combined the two IF statements needed to be adjusted, the Syntax.

In your first example, you can "closed off" the first IF statement with a closing parentheses. To add IFs together, you'll need to keep the closing parentheses to the very very end of the entire statement (2 or 8 IFs down the line, depending on how long your statement is).

You also need to repeat the IF function itself, after a comma, which you left out of your first example as well.

IF on its own:

=IF(logic statement, then value if true, then value if false)

=IF(OR([Place in Funnel]2 = "Universe", [Place in Funnel]2 = "Above"), "0%")

IF added with another IF

=IF(logic statement, then value if true, IF(Logic statement, then value if true, then value if false)) <two closing parentheses

=IF(OR([Place in Funnel]2 = "Universe", [Place in Funnel]2 = "Above"), "0%" , IF(AND([Place in Funnel]2 = "In", [Sales Stage]2 = "Lead"), "10%"))

So if you had 8 IFs, at the end of your statement you would have 8 closing parentheses. Does that make sense? Should you have each of the 8 statements separated, we'd be happy to help you put them all together and get the correct syntax.

You may also want to take a look in the Help Center (see here), or watch some of the webinars on formulas (see here).

Cheers,

Genevieve

Heather Simmons

Thank you Genevieve, it worked! Below are the other IF statements I need included...

IF([Place in Funnel]2 = "In", [Sales Stage]2 =

"Opportunity", "20%"

IF([Place in Funnel]2 = "In", [Sales Stage]2 =

"Qualifying", "50%"

IF([Place in Funnel]2 = "In", [Sales Stage]2 =

"Negotiation", "70%"

IF(OR([Place in Funnel]2 = "In", [Sales Stage]2 = "Proposal –

Individual for Job", "MSA", "Agreement",

"80%"

IF([Place in Funnel]2 =

"Closed", "100%"

Genevieve P.

Hi @Heather Simmons

Try this, I've just added them together:

=IF(OR([Place in Funnel]@row = "Universe", [Place in Funnel]@row = "Above"), 0 , IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Lead"), 0.1, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Opportunity"), 0.2, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Qualifying"), 0.5, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Negotiation"), 0.7, IF(AND([Place in Funnel]@row = "In", OR([Sales Stage]@row = "Proposal – Individual for Job", [Sales Stage]@row ="MSA", [Sales Stage]@row ="Agreement")), 0.8, IF([Place in Funnel]@row= "Closed"),1)))))))

You will note that I used @row instead of saying 2 after each cell. Using @row means that you can have this be a Column Formula (see here).

It also looks like you want to output a percentage. To do so in formula, you want to use decimals. For example, instead of "10%", say 0.1. This will provide a numerical output that you can then configure to be a % by using the % formatting on the entire column. Doing it this way will allow you to create numerical calculations (like Averages) which you wouldn't be able to do with "10%" as that's read as text. Our Help Center has more information on Percents in Formulas (see here).

Let me know if this works for you!

Cheers.

Genevieve

Heather Simmons

Thanks @Genevieve P!

I tried it and ended up with the UNPARSEABLE error message. Would it be helpful if I send you a link to the Smartsheet document to try it there?

THANKS!

Heather Simmons

Genevieve P.

Hi @Heather Simmons

My apologies! I closed off the last IF statement but there was no AND as a part of it, so that closing parentheses needs to go at the very end along with all the others.

Try this:

=IF(OR([Place in Funnel]@row = "Universe", [Place in Funnel]@row = "Above"), 0 , IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Lead"), 0.1, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Opportunity"), 0.2, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Qualifying"), 0.5, IF(AND([Place in Funnel]@row = "In", [Sales Stage]@row = "Negotiation"), 0.7, IF(AND([Place in Funnel]@row = "In", OR([Sales Stage]@row = "Proposal – Individual for Job", [Sales Stage]@row ="MSA", [Sales Stage]@row ="Agreement")), 0.8, IF([Place in Funnel]@row= "Closed",1))))))))

Heather Simmons

@Genevieve P this worked, thank you for all of your help!

Genevieve P.

No problem at all, I'm glad we could get it working for you. 🙂