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 MultiYear 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 MultiYear Project]row, IF(ISBLANK([22. Advisory: Length of MultiYear Project]@row), "12", "")))))
Best Answer

Excellent!
Glad we got it working!
✅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.
Answers

You are missing the @ symbol from this section:
[22. Advisory: Length of MultiYear 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 MultiYear Project]@row, IF(ISBLANK([22. Advisory: Length of MultiYear 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 MultiYear Project]@row,
IF(ISBLANK([22. Advisory: Length of MultiYear 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 MultiYear 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"

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 MultiYear Project]@row, IF(ISBLANK([22. Advisory: Length of MultiYear 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 MultiYear Project]@row, IF(ISBLANK([22. Advisory: Length of MultiYear 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 MultiYear 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 MultiYear 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 MultiYear Project]@row))), [22. Advisory: Length of MultiYear 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!
Glad we got it working!
✅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
Categories
Check out the Formula Handbook template!