Sheet summary formula to SUM a column where cells are formulas
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).
Best Answers

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))

Yes, all of your the formulas in your Payment column need to be rewritten without the doublequotes 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.
cheers
Kelly
Answers

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))

@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.

Hi, Christie.
As @KDM pointed out, in your formula, remove the doublequotes from around the numbers. Use her formula...
=IF([Item Type]@row = "Stand Alone", 150, IF([Item Type]@row = "Unfolding", 450))
Doublequotes 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 doublequotes and then treat them as text depending on the usage context. Smartsheet doesn't do that.

Yes, all of your the formulas in your Payment column need to be rewritten without the doublequotes 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.
cheers
Kelly

Thank you both! That worked!
Help Article Resources
Categories
Check out the Formula Handbook template!