Multiply Cost and Quantity, but if another column is not empty... use that override value

[jord8on]

• We have a field called Products which is a link to our “Product” table
• We have a field called Cost which is a lookup field. (looks up the cost from the Product table)
• In our “ORDER” table, we have a field called Total Cost

In that Total Cost field we would like to multiply the Cost by the Qty (quantity) but if there is a value in the Misc Cost field then we would like to multiply that field by the Qty to calculate the Total Cost

Here’s the kicker…

Basically, we want to use the Misc Cost to override the Cost x Qty total, if there if something is entered in that field.

Usually a product is full price for us. Sometimes we get a massive discount on a certain quantity or we get some of a product for free. I still want to link to the existing product from our database, but I want to have this Override field and have my “Total Cost” field use the Misc Cost field, if it’s exists.

Sorry if I didn’t explain this very well. Any help here would be great!! :pray:

[ScottWorld]

That could be done with a simple formula:

IF({Misc Cost},Qty*{Misc Cost},Qty*Cost)

[jord8on · Accepted solution]

Thank you so much @ScottWorld!

That formula was much further than I was ever able to get, so thank you! But when I use that formula and put a $0.00 in the Misc Cost field, the Total Cost field, I want that $0.00 to override the Cost which it was not doing.

After a bit of tinkering a friend helped me come up with this, which is exactly with what I need…

:white_check_mark: . SOLUTION…
IF({Misc COST}!=BLANK(),{Misc COST}*Qty,Cost*Qty)

[ScottWorld]

Ah, yes! Zero is the tricky one. Glad you figured it out! :slightly_smiling_face: