Help with formula

flee

Please see the attached screen shot. With my current formula it returns a 0. Appreciate the help.

Foster

ss.PNG

flee

Adding to the above post. The first set of multiplication works. Then Second set doesn't. Meaning if I have numbers in 'Qty of Work Paint' and 'Flag Hours Paint' (which is populated using an INDEX formula) I get the correct total. If you input a number 'Qty of Work Paint' and 'Custom Flag Hour' it returns a 0.

Foster

flee

Another update. The formula issue described above "fixes" itself if I don't put anything in the 'Work Completed Paint' column. This column is a drop down not restricted to drop down choices.

That column isn't used in the formula anywhere. Why would it case a problem?

Thanks again for any help.

Foster

Mark.poole

@flee

Good Morning. The issue appears to be very simple syntax issue with the sum function in the formula causing an error. thus the iferror is triggering. With Sum the syntax is SUM(quantity1, quantity2) You have a + instead of the ,. So the options are to either replace the + or remove the Sum function.

=IFERROR(SUM(([Qty of Work Paint]@row *[Flag Hours Paint]@row), (Qty of Work Paint]@row * [Custom Flag Hour]@row)), "0")

or

=IFERROR(([Qty of Work Paint]@row *[Flag Hours Paint]@row) + (Qty of Work Paint]@row * [Custom Flag Hour]@row), "0")

flee

Thank you for your help Mark. I understand what you are saying above and appreciate the correction.

This still do not solve my problem. After updating the formula by removing the "+" sign it is still doing the same thing. The calculation works fine as long as nothing is in the 'Work Competed Paint' column. As soon as I put and entry in this column the 'Paint Total' changes to 0.

Best

Foster

Paul Newcome

What do you get when you get rid of the IFERROR?

flee

Paul see the attached picture. I get a #INVALID OPERATION error. This error is why I added the 'IFERROR' to my formula.

Best

Foster

Mark.poole

@flee

I see the issue Work completed paint is being used in a formula that provides a number it appears. If that is the case you need to wrap that formula where ever it is into a value function to make it work for sum

flee

Mark, unless I am missing something (which is entirely possible) the 'Work Completed Paint' column isn't involved in the formula at all. This column's properties is a drop down that allows other entries. When you select from the drop down the value for 'Flag Hours Paint' is pulled in using an INDEX formula. If you manually enter a response in the 'Work Completed Paint' column then you would manually enter a value in the 'Custom Flag Hour' column.

The purpose is to calculate the value for the 'Paint Total'. I do not understand why making a entry in the 'Work Completed Paint' column is causing the calculation to not be made when it is not part of the calculation formula.

Hope I am explaining this clearly.

Foster

Mark.poole

@flee Try this instead.

=IFERROR(IF(NOT(ISBLANK([Custom Flag Hour]@row)), [Qty of Work Paint]@row * [Custom Flag Hour]@row, [Qty of Work Paint]@row *[Flag Hours Paint]@row), 0)

See if this formula achieves what it is your wanting.

flee

Thank you Mark! Your formula works.

Can you explain what was causing the error in my formula.

Thanks again

Foster

Mark.poole

@flee

The issue was you were trying to multiply Qty of Work Paint by a blank cell.

So to fix the issue I have it checking first to see if the custom flag column had an entry. If so it always multiplies by it. If not then it checks the flag hours paint for a value. and multiplies by it. If there is no value in either case then it throws the error and provides the 0.

flee

Thank you very much for you help and explanation.

Much appreciated.

Foster