Multiple If statements

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

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

All comments

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?

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.

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

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

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

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%"

Try this, I've just added them together:

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

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

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:

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