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

Question

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

Jeff Reisman · Accepted 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.)

Jeff Reisman · Accepted 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.