Multiple If statements

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

Best Answers

Answers

  • Mark Cronk
    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?


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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.

  • 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.
    Genevieve P. Employee Admin
    edited 11/16/20

    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

  • 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 this worked, thank you for all of your help!

  • Genevieve P.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!