# Multiple IF Statements for Various Scenarios

✭✭✭✭

Hello! Based on multiple criteria, I'm building a formula to yield a completion %:

I want the results to yield 0% if a specific field is marked "No" or another field is marked "No" or "N/A", populate 50% (.5) if one of two criteria are met, and populate 100% (1) if all criteria are met.

I started building the formula below:

=IF([LTC Target]@row = "No", 0), IF(OR([LTC Intro Meeting Completed]@row = "N/A", [LTC Intro Meeting Completed]@row = "No"), 0), IF(AND([LTC Intro Meeting Completed]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes") 1)

I've also attached the sheet I'm working with for reference. Any help is much appreciated!

Tags:

• ✭✭✭✭✭✭

In a nested IF formula, you don't put the ) at the end of each IF statement, just a , and then at the very end, put the needed ) for each of the IF statements.

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭✭✭✭✭

Try this: =IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1, IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5, IF(AND(OR([LTC Intro Meeting Complete]@row = "No", [LTC Intro Meeting Complete]@row = "N/A"), [LTC Quote / Proposal Issued]@row = "Yes"), 0.5, IF(AND([LTC Target]@row = "No", [LTC Intro Meeting Complete]@row = "No"), 0, 0))))

• ✭✭✭✭
edited 06/16/23

@Nic Larsen thanks for jumping in on this! I tried your formula, but I'm still receiving a UNPARSEABLE error. Thoughts?

• ✭✭✭✭✭✭

Its going to be the AND statements and the OR statements where your issue is coming from. Let me look at these two formulas again and I will figure it out. I have some similar formulas that might be able to be adjusted to fit your use case.

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭✭✭✭✭

@Tony Fronza have you tried breakout out the formula into separate parts to see where the error is? example

=IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1,0) and see if it works appropriately then

IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5,0) until you check all the If statements individually. It's usually a comma or a ) out of place that's the easiest way I have found to discover errors in long formulas.

• ✭✭✭✭✭✭
edited 06/16/23

I don't think this formula will work, but you can try it. It is Nick's with an adjustment.

=IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1, IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5, IF(AND(OR([LTC Intro Meeting Complete]@row = "No", [LTC Intro Meeting Complete]@row = "N/A"), [LTC Quote / Proposal Issued]@row = "Yes"), 0.5, IF(AND([LTC Target]@row = "No", [LTC Intro Meeting Complete]@row = "No"), 0))))

I think having the AND(OR is causing the issue in this formula though.

Edit:

Here is another formula for you to try.

=IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "Yes"), 1, IF(AND([LTC Intro Meeting Complete]@row = "Yes", [LTC Quote / Proposal Issued]@row = "No"), 0.5, IF(AND([LTC Intro Meeting Complete]@row = "No", [LTC Intro Meeting Complete]@row = "N/A"), 0.5, IF(AND([LTC Intro Meeting Complete]@row = "No", [LTC Quote / Proposal Issued]@row = "Yes"), 0.5, IF(AND([LTC Target]@row = "No", [LTC Intro Meeting Complete]@row = "No"), 0)))))

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭✭✭

While thinking through this, I want the formula to reflect a % if the field is NOT blank (much easier logic) for three fields (i.e., 33% for the first field, 33% for the second field, and 34% for the third to = 100). Could someone help me write this formula?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!