Why won't my multiplication formula work?

Allie Forbes
Allie Forbes ✭✭✭
edited 10/08/20 in Formulas and Functions

I am trying to enter a formula that will multiple the contents of two cells in a row in a third cell by using =[cell 1] * [cell 2]. This worked when I had manually entered the numbers into cells 1 and 2. I changed cell 2 contents to a formula so that I do not need to manually update that column. Although this formula works in cell 2, it broke the formula in cell 3, #INVALID OPERATION...

Just for fun, I changed the * to a + in cell 3 to see how/if that would affect the error. This formula was successful in joining the two numbers to 2147 (not actually adding them). Does anyone know why I would be facing this problem?

This first picture is the formula in cell 3 that is broken (the reason the top row doesn't have an error is because I manually entered the 2 in the [Tests run per sample] cell):

This second picture is the formula in the [Tests run per sample] cells:


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Because you're dealing with cells the simple math operators act a little diifferently. Try this formula...

    =Value([Cell 1]@row) * Value([Cell 2@row])

    The + operator is working like a join where its just putting the two cell values next to each other.

    Did that help?

  • Same problem. My cell issue is the following,

    I have a function that says if "Jacket" then the cell automatically calculates $16.00. When I put in a quantity and try to multiply it to the 16.00 it says invalid function.

    =IF(CONTAINS("Jacket", [Clothing Item]:[Clothing Item]), "$16.00")

    =Quanity@row*[Unit Cost]@row <---------Ends up being INVALID FUNCTION

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭


    I hope you're well and safe!

    Try something like this.

    =IF(CONTAINS("Jacket", [Clothing Item]:[Clothing Item]), 16)

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!