# Need assistance with a formula not working properly - Thank you in advance!

Hello everyone! I am hoping someone can assist me with identifying why this formula will not return "12" when [22. Advisory: Length of Multi-Year Project]@row is blank? What am I doing wrong? The formula correctly returns all other values. Any help would be greatly appreciated!

=IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", "3", IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", "4", IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", "2", IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), "12", "")))))

• ✭✭✭✭✭✭

Excellent!

Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

You are missing the @ symbol from this section:

[22. Advisory: Length of Multi-Year Project]row

If it still does not work, you may try plugging this formula in to verify the cells you believe to be blank are actually blank. If that column is populated through a formula, data shuttle, etc, they can sometimes be misleading.

• ✭✭✭✭✭✭

I hope you're well and safe!

You were missing one @.

Try something like this.

=IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", "3", IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", "4", IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", "2", IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), "12", "")))))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Thank you so much for the try! Now it is only returning "12" and nothing else. Any other suggestions? Really appreciate the try!

• ✭✭✭✭✭✭

You're more than welcome!

Can you write out what your formula should do? I'm not sure it's correct.

Should the bolded section not have a number as the answer?

=

IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", "3",

IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", "4",

IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", "2",

IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row,

IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), "12", "")))))

Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• @Andrée Starå, thank you so much! If the field [22. Advisory: Length of Multi-Year Project]@row has a number, we want it to return that number in that field, if it is blank, then we want it to return "12". Does that make sense?

• ✭✭✭✭✭✭

If you are always getting "12" as a result, none of the statements for [22a. Advisory: How will revenue be forecasted?]@row are matching. Have you double checked to make sure there are no typos in those checks?

"40/35/25 - initial with downward"

"15/30/35/20 - plan, execute, close"

"70/30 - 1st Half"

"Evenly - fees divided by the number of months"

• edited 04/05/24

Evenly - fees divided by the number of months

40/35/25 - initial with downward

15/30/35/20 - plan, execute, close

70/30 - 1st Half

Copy and pasted from the dropdown list. I have checked these many times as well. Not sure why they are not working. Now the formula is returning 12 for all except it is returning blank for "Evenly..." which should return "12". Do you think the formula is in the wrong order?

=IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", 3, IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", 4, IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", 2, IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), 12, "")))))

• UPDATE: @Carson Penticuff and @Andrée Starå this now returns "12" for everything:

=IF([22a. Advisory: How will revenue be forecasted?]@row = " Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), 12, IF([22a. Advisory: How will revenue be forecasted?]@row = " 40/35/25 - initial with downward", 3, IF([22a. Advisory: How will revenue be forecasted?]@row = " 15/30/35/20 - plan, execute, close", 4, IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", 2)))))

Im at a loss. Thanks anyone for any insight or suggestions!

• ✭✭✭✭✭✭

Is the leading space in each of these intentional?

" Evenly - fees divided by the number of months"

" 40/35/25 - initial with downward"

" 15/30/35/20 - plan, execute, close"

In order to always return 12, it leads me to believe [22. Advisory: Length of Multi-Year Project]@row is always viewed as blank.

Are you able to post a screenshot of your sheet layout? (redacted if necessary)

• FINAL UPDATE @Carson Penticuff and @Andrée Starå: This formula now works! Thank you all for your suggestions and input! Please notice the "NOT" added and seemed to do the trick! Have a lovely rest of your Friday and weekend!

=IF(AND([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", ISBLANK([22. Advisory: Length of Multi-Year Project]@row)), 12, IF(AND([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", NOT(ISBLANK([22. Advisory: Length of Multi-Year Project]@row))), [22. Advisory: Length of Multi-Year Project]@row, IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", 3, IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", 4, IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", 2, "")))))

• ✭✭✭✭✭✭

Excellent!

Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!