Why are values acting like text and how do I rectify it?

My (Costing) formula was working yesterday until late in the afternoon then something happened. The formula is supposed to SUM the 'Cost' cells which have calculate the ‘material’ value (light green columns with fx) on a project. The individual cells should already be values, however these cells are acting like text.

The formula reads; =([PR1-Cost]@row + [PR2-Cost]@row + [SP-Cost]@row + [IN1-Cost]@row + [IN2-Cost]@row + [BN1-Cost]@row + [BN2-Cost]@row + [CO1-Cost]@row + [CO2-Cost]@row)

When I insert SUM at the beginning of the formula it results in $0.00

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Scott Cornish

    The answer is going to lie in the formulas you're using to get the individual values.

    The way Smartsheet handles addition is that if there is any text value, even a blank space or a number represented as text, among the values you're trying to add, it will concatenate instead of add, and the result it treats like text as well. So check all your Cost formulas to see which is ending up as a text value.

    If you're using IF statements or anything like that in calculating your various cost cells, check to make sure you're not setting a false condition as "", or " ". Either use 0, or just don't put a false condition at all (which Smartsheet treats as literally nothing, no value at all in the field.)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 07/08/22 Answer ✓

    @Scott Cornish

    Perhaps this is the first time you've had an error result in the formula?

    Is there a completely empty blank text/number cell on your sheet that you can reference? Maybe add a hidden helper column for this if needed? If so, we can get around errors resulting in a 0 value in the field. Replace the "" at the end with the cell reference for that completely empty cell:

    =IFERROR((INDEX({PC | Material Price}, MATCH([PRINT-1]@row, {PC | Material Data Entry}, 0))) * [PR1-Units]@row, HelperColumn1)

    where HelperColumn1 is the completely empty blank text/number cell. This sets the cell in error to be nothing at all instead of an empty text cell, and allows Smartsheet to treat the addition formula as numbers and not text.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Scott Cornish

    The answer is going to lie in the formulas you're using to get the individual values.

    The way Smartsheet handles addition is that if there is any text value, even a blank space or a number represented as text, among the values you're trying to add, it will concatenate instead of add, and the result it treats like text as well. So check all your Cost formulas to see which is ending up as a text value.

    If you're using IF statements or anything like that in calculating your various cost cells, check to make sure you're not setting a false condition as "", or " ". Either use 0, or just don't put a false condition at all (which Smartsheet treats as literally nothing, no value at all in the field.)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • @Jeff Reisman

    First, thank you for responding so quickly.... My Cost formulas are written; =IFERROR((INDEX({PC | Material Price}, MATCH([PRINT-1]@row, {PC | Material Data Entry}, 0))) * [PR1-Units]@row, "")

    If I understand you correctly, I should replace the false condition "" with 0 (zero). This would fill the column with $0.00 unless there is material (and quantity) entered to calculate, which I was trying to avoid so that I would have a quick visual that the fields were empty, like the rows above this one.

    Your answer makes complete sense and definately does work. I do not recall any information like that when I was researching formula writting. What doesn't make sense is that I have had this sheet functioning for months now and this was somehow triggered yesterday. Additionally, I am using similar formulas across this sheet (and others) that have not been an issue. Any idea on how it may have been triggered?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 07/08/22 Answer ✓

    @Scott Cornish

    Perhaps this is the first time you've had an error result in the formula?

    Is there a completely empty blank text/number cell on your sheet that you can reference? Maybe add a hidden helper column for this if needed? If so, we can get around errors resulting in a 0 value in the field. Replace the "" at the end with the cell reference for that completely empty cell:

    =IFERROR((INDEX({PC | Material Price}, MATCH([PRINT-1]@row, {PC | Material Data Entry}, 0))) * [PR1-Units]@row, HelperColumn1)

    where HelperColumn1 is the completely empty blank text/number cell. This sets the cell in error to be nothing at all instead of an empty text cell, and allows Smartsheet to treat the addition formula as numbers and not text.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • @Jeff R

    That is a great idea. Thank you again...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!