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

✭✭✭✭✭
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

Mike is correct (I believe). The #INVALID OPERATION is due to the multiplication.

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/weighted-average-sumproduct

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

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 in-system 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.

This discussion has been closed.