SUM formula with extending range
Is it possibly to have a SUM formula in a cell at the top of a column, calculating the sum of the contents of that said column, with the range beginning at a specific row but continuing to the bottom of the sheet and extending down as rows are added to the sheet automatically? It would obviously be easier for me to just have the formula in sheet summary, however for viability purposes it be beneficial for me to have the formula at the top of the column. Be glad of anyone's ideas.
Answers

Are you trying to SUM the entire column?

No just from a specific row down. So if I have the formula in Column1 Row1 I want it to SUM Column1 Row3 down to the bottom of the sheet, and for the SUM to include any new rows that are added automatically via the workflow i have in place.

You would need an additional "helper" column that will designate which rows to SUM and then use a SUMIFS instead. The helper column could be a column that basically replicates the row number. You would put this formula in Row 1 and dragfill down. New rows should trigger autofill.
=COUNTIFS([Other Column Name]$1:[Other Column Name]@row, OR(@cell = "", @cell <> ""))
Then to sum everything from row 3 down, you would use something along the lines of...
=SUMIFS([Sum Column]:[Sum Column], [Helper Column]:[Helper Column], @cell >= 3)

Was looking for the same thing because I wanted to have a 'rollup' at the top of my sheet summing specific variables in one column along with the value in a corresponding column. I'm adding rows to the sheet via a zap, and finally realized (actually after reading this) that I just needed to create a couple new columns so that I could run my SUMIFS formula from Column:Column (top to bottom) without circular references.
Formatting added for sharing purposes... now it looks like this, where my far right columns run SUMIFS for each of the task names and then update total value per task as the sheet grows. Not the most ideal or pretty, but gets the job done, and allows me to then target that summed area from a dashboard!

Hi @BrandIMPs
I hope you're well and safe!
Another option could also be to use the Sheet Summary section instead, and then it won't clutter the sheet, and you can bring it up wherever you are in the sheet.
Make sense?
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Thanks for sharing your example syntax. I am trying to accomplish exactly what @Jonnie White is asking. Unfortunately, I'm not getting it to work.
I could get the "Helper" column working which basically returned a number that matches the row number. 👍🏻
When I add in the "=SUMIFS([Sum Column]:[Sum Column], [Helper Column]:[Helper Column], @cell >= 3)" syntax… the Sum column returns #BLOCKED and then the entire Helper column changes to #CIRCULAR REFERENCE.

@Reliability Engineer The SUMIFS would need to go into a different column.
Help Article Resources
Categories
Check out the Formula Handbook template!