Sheet summary formula to SUM a column where cells are formulas

Christie Hart

In my sheet summary, I am trying to sum/total a column where the cell values are populated as a result of a formula. I can't seem to get a value.

The formula for the cells I am trying to sum is this: =IF([Item Type]15 = "Stand Alone", "150", IF([Item Type]15 = "Unfolding", "450"))

It is referring to one other column.

I want to sum all of the values in the column that are populated by that formula. I keep getting "Unparseable" or a value of zero, or some other error (invalid data type, maybe).

Kelly Moore

Hey @Christie Hart

Remove the quotes around your numbers. Quotes may make a number behave as if it were text.

=IF([Item Type]15 = "Stand Alone", 150, IF([Item Type]15 = "Unfolding", 450))

Also, unless it is necessary to specifically reference row 15, Smartsheet encourages the use of @row in place of row numbers

=IF([Item Type]@row = "Stand Alone", 150, IF([Item Type]@row = "Unfolding", 450))

Kelly Moore

Hi @Christie Hart

Yes, all of your the formulas in your Payment column need to be re-written without the double-quotes around the numbers. Currently your SUM function thinks it is trying to sum text values.

Since you removed the row number you can now easily populate the entire column with the correct formula. Do this by RIGHT CLICKING in any cell where the double quotes have been removed AND the row number has been converted to @cell. The cell menu will pop up and at the bottom of that menu is convert to Column Formula. This will automatically populate the entire column will the formula in that cell. NOTE: to edit or remove the formula after it has been converted to a column formula you must again RIGHT CLICK in a cell and select EDIT column formula or Convert to cell formula.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

cheers

Kelly

Kelly Moore

Hey @Christie Hart

Remove the quotes around your numbers. Quotes may make a number behave as if it were text.

=IF([Item Type]15 = "Stand Alone", 150, IF([Item Type]15 = "Unfolding", 450))

Also, unless it is necessary to specifically reference row 15, Smartsheet encourages the use of @row in place of row numbers

=IF([Item Type]@row = "Stand Alone", 150, IF([Item Type]@row = "Unfolding", 450))

Christie Hart

@KDM That formula is actually doing what I want it to do. What I want to do is total the Payment Colum in the Sheet Summary. If I SUM(Payment:Payment) I only get a zero, and I know that's not correct.

Every cell in the payment column is calculated that way. Maybe that needs to change? I did change to @row vs the row number per your suggestion.

Toufong Vang

Hi, Christie.

As @KDM pointed out, in your formula, remove the double-quotes from around the numbers. Use her formula...

=IF([Item Type]@row = "Stand Alone", 150, IF([Item Type]@row = "Unfolding", 450))

Double-quotes around anything converts it into text (a.k.a., "string" to programmers). The numeric value of a text/string is zero.

Some programs will intuitively perform math operations with numbers contained within double-quotes and then treat them as text depending on the usage context. Smartsheet doesn't do that.

Kelly Moore

Hi @Christie Hart

Yes, all of your the formulas in your Payment column need to be re-written without the double-quotes around the numbers. Currently your SUM function thinks it is trying to sum text values.

Since you removed the row number you can now easily populate the entire column with the correct formula. Do this by RIGHT CLICKING in any cell where the double quotes have been removed AND the row number has been converted to @cell. The cell menu will pop up and at the bottom of that menu is convert to Column Formula. This will automatically populate the entire column will the formula in that cell. NOTE: to edit or remove the formula after it has been converted to a column formula you must again RIGHT CLICK in a cell and select EDIT column formula or Convert to cell formula.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

cheers

Kelly

Christie Hart

Thank you both! That worked!