Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Sum formula Error When References Results from IF Formula
I am getting an #Invalid Operation error when my Sum formula references a cell with results from an IF formula:
=SUM([Column 8]1 * [Column 9]1) results as #Invalid Operation error
Column 9 formula
=IF([Column 2] = "A", "0.5%", IF([Column 2] = "B", "1.5%"))
I've tried to fix by using a nested VALUE= but that does not work either. Is there anything I can do to fix it?
Thank you greatly for any help.
Gladys
Comments

Hi, Gladys, based on the asterisk in your formula it appears that you are trying to multiply the two fields together. You should be able to accomplish what you are looking to do using either of these formulas:
=[Column8]1 * [Column9]1
If you're looking to add them together use this formula,
=SUM([Column 8]1, [Column 9]1)
Here is more information about the sum formula: https://help.smartsheet.com/function/sum

Gladys,
Mike is correct (I believe). The #INVALID OPERATION is due to the multiplication.
In addition
VALUE("1.5%") will return #INVALID VALUE
The VALUE() function will return the numeric value of text IF IT CAN. But since % is not a number, it can not in this case.
For your formula in column 9, return the number and then format it for percentage (you may need to add a decimal to the standard percentage format)
=IF([Column 2] = "A", 0.005, IF([Column 2] = "B", 0.015))
For follow on formulas, be aware that 1% is 0.01 value.
Lastly, if you are trying to perform a SUMPRODUCT type function, see this post:
https://community.smartsheet.com/discussion/weightedaveragesumproduct
I hope this helps.
Craig

Craig & Mike,
It worked!
I removed the SUM from the formula, removed the parenthesis ("") from the Column 9 formula around the % value and changed the column type to %...wallla. You guys are best!
Thank you again!

You are welcome.
Craig

Gladys,
Good catch Mike and Craig, sometimes it's hard to see the obvious.
I would offer a friendly suggestion with the IF formula as you are using it ...End the formula with instructions if the entry in column 2 is not A or B. Below I added a dash in parenthesis, or you could use "error", or "Enter A or B in Column 2", or any other message. Having the formula put something in the cell and not just let it be blank helps to not mistake the cell as a blank cell and accidentally overwriting the formula.
I was actually surprised to see that the IF formula worked without this final instruction.
=IF([Column 2] = "A", 0.005, IF([Column 2] = "B", 0.015,""))
Have fun.
Dan Davis

Good point Dan.
Every such decision must be weighed against the insystem design.
For example, if [Column 2] is a drop down list, then an error is on the input, not the output, so I would do something else to find and fix that (the recent data validation changes for example)
As the original post says, there was a formula somewhere that was summing the results column. That means blank or "" would be OK, but something else like
=COUNTIF(Results:Results, VALUE(@cell) > 0)
would toss an error.
Also, because of the SUM() after, the flag for missed entry might be overlooked as it is one step removed from the cause.
There are plenty of guidelines but fewer hard and fast rules.
When answering questions, I try to focus on the requirements, sometimes asking questions if a requirement seems to be missing.
Craig

Good point Craig. I agree with your statement about trying to focus on the requirements. Sometimes that's all that's needed. Sometimes the output then reveals further questions. I tend to address those when they come up, unless there are clear signs of other issues that could arise  then I bring attention to those right away.
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives