Multiple IF statments
I have a probability column (High, Med, Low).. when updated to one of these I want it to apply a predetermined percentage to a sum of rows (projected sales).
Example, Projected Sales =Sum of columns 14, If High I want the sum to be multiplied by .75, if Med by .5 and if Low .25.
I can get this formula to work for one variable (High in this case), but cannot add the same parameters for Med and Low.
=IF(Probability@row = "High", SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * 0.75) .. that works fine but when I add additional IF statements in the same format it gives me an error.
=IF(Probability@row = "High", SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * 0.75), IF(Probability@row = "Medium", SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * 0.5) and I get an error.
Please advise, am I using this formula incorrectly or am I just using the wrong formula? Can Smartsheet do what I want in this case?
Answers

Let's separate the two a little bit to save you some key strokes. If we do the SUM
SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row)
and then multiply by the nested if, you only need to key the SM portion once.
=SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * IF(Probability@row = "High", 0.75, IF(Probability@row = "Medium", 0.5, 0.25))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

It says unparseable.

I got it to work.. needed some extra parentheses at the end.
=SUM([Q1 2021 Closings]@row:[Q4 2021 Closings]@row) * (IF(Probability@row = "High", 0.75, IF(Probability@row = "Medium", 0.5, IF(Probability@row = "Low", 0.25))))

It looks like you used an extra opening parenthesis before the IF statement, and you used an additional IF statement as well which would lead to the additional closing parenthesis being needed.
Really though the set of parenthesis isolating the nested IF shouldn't have been necessary because even though it is nested it is still a single output.
And the 3rd IF statement shouldn't have been needed either since we used the final portion of the second IF to cover the 3rd output.
Were you getting an error with my formula? I am curious as to why it didn't work.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Yes, when I copied/pasted your formula it gave me the #unparseable error.

Ah. It looks like there may have been a change in column names?
Your original post has "[Q1 2021 Sales]" whereas the formula you posted that you said was working for you has "[Q1 2021 Closings]".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Ah, that would do it. I changed the column headings. Either way, it is working now. Thanks!

Ok. Phew. Glad I wasn't losing it. Haha.
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!