How to apply numeric formula automatically to a sheet

AngelaC

Hello, I am new to Smartsheet,

Need is: I want to be able to see a rollup of the order amounts in my sheet. Is there a way to add the totals for each order amount in my Order column so I can see the total amount? Can you send me an example of what the correct formula would look like?

I have determined the formula to add up the total of each column for each row horizontally on a sheet. However, their must be a way to apply that formula to the sheet automatically so I do not have to pull down the formula in the Total Orders column...and then is their a way to total the Total Orders column for the grand total at the bottom of the Total Orders column ongoing?

I reviewed all formula links, and tried the @row (gives me an error) and does not seem to apply to a range of numbers?

Update - I now see that the formula does save and if you add rows above it will auto calculate them - sooo, I may have answered my own question 🙂 please let me know if I there is another way to do this that I am not aware of... - Thank U!

I appreciate your assistance as I would like to answer this question correctly,

Thank you so much,

AngelaC

Mike Wilday

Are you getting a Circular Reference error?

This is probably because the sum formula is also trying to sum the the sum formula's result.

Have you considered using summary sheet cells to roll up the total data? This will keep your sum off of the sheet data and give room for growth in row count.

For more on sheet summary see:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

AngelaC

Thank you for your help Mike! This is the difference between excel formulas and the ease of smartsheet!

My understanding is I just Add a new field, name it and then add the formula to each field and that will keep the roll up totals in the sheet summary vs. the actual sheet.

There are a lot of great features to learn... - Thanks again! - AngelaC

Regarding the formula error I am receiving #PARSEABLE, it may the formats entered are not quite right?

I will review the formula error message learning center link you sent along as well...

Take good care.

Mike Wilday

Yes, those look right. You only use the [ brackets ] when there is a space in the column name or if there is a number at the end of the column name. Your first reference will sum the entire column. The second one will sum across row 1 and whatever range of columns is between the ones you referenced. Does that make sense?

Mike Wilday

Are you getting a Circular Reference error?

This is probably because the sum formula is also trying to sum the the sum formula's result.

Have you considered using summary sheet cells to roll up the total data? This will keep your sum off of the sheet data and give room for growth in row count.

For more on sheet summary see:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Mike Wilday

Here is information on the #Circular Reference error:

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--circular-reference

AngelaC

Thank you for your help Mike! This is the difference between excel formulas and the ease of smartsheet!

My understanding is I just Add a new field, name it and then add the formula to each field and that will keep the roll up totals in the sheet summary vs. the actual sheet.

There are a lot of great features to learn... - Thanks again! - AngelaC

Regarding the formula error I am receiving #PARSEABLE, it may the formats entered are not quite right?

I will review the formula error message learning center link you sent along as well...

Take good care.

AngelaC

Another note, you still need to know how to determine the formulas to enter... if you were someone that did know or have worked with formulas is their an easy way to program those into those fields?

Thank you, AngelaC

Mike Wilday

Can you share the formula as you've entered it? In Smartsheet you reference column names and row numbers instead of letters and numbers. If you want to share the formula you are trying to use, I would be happy to review it.

AngelaC

Based on the question, I am not sure if they want to add up one column or a range of columns and rows, so I tested both formulas below. If you were advising the Smartsheet reference column names and row numbers are the below formats correct?

=SUM([Column Name]:[Column Name])

and/or

=SUM([Column Name 1]1:[Column Name 3]1

Thank you for your help!

AngelaC

Mike Wilday

Yes, those look right. You only use the [ brackets ] when there is a space in the column name or if there is a number at the end of the column name. Your first reference will sum the entire column. The second one will sum across row 1 and whatever range of columns is between the ones you referenced. Does that make sense?