SUM formula with extending range

Jonnie White

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.

Paul Newcome

Are you trying to SUM the entire column?

Jonnie White

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.

Paul Newcome

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)

BrandIMPs

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!

Screen Shot 2021-04-27 at 6.14.41 PM.png

Andrée Starå

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!

Reliability Engineer

@Paul Newcome ,

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.

Paul Newcome

@Reliability Engineer The SUMIFS would need to go into a different column.