How to apply numeric formula automatically to a sheet
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
Best Answers
-
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:
-
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.
-
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?
Answers
-
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:
-
Here is information on the #Circular Reference error:
-
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.
-
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
-
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.
-
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
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!