Sum the rest of a column

Bec Sullivan

Hi All,

I am trying to sum the rest of a column (see below). This is in the creation of a a blueprint workspace, so there is no data in there and so please ignore the divide by zero errors.

I have 2 sections for project costs, one for labour costs which is based on a very simple salary*timeframe formula, and then a second section for all other itemised costs. I would like the sum the other costs in the highligted cell, based on all the numbers that will be put in the rows below.

I have tried setting up the template to treat all the rows below "Other Costs" as children becuase I can then use the sum children formula, but I am unable to indent blank rows (and as this is a template, they need to stay blank for the projec to fill in as required). I also tried to sum the whole column and then minus cells above, but I got a circular reference error.

What I thought I could do is a =SUM([Amount (excl GST)]21:[Amount (excl GST)]) - where 21 is the row the data will start from, and then to the end of the sheet. But this is not working either.

Any advice on a formula that will give me the desired outcome? Thanks in advance.

Jeremy_D

I have a solution but you'll need a few helper columns. Here's the steps:

1. Create a column called 'Auto No' and select the type as 'Auto number' (this generates a number for each row).

2. Create another column called 'Row No' and insert this formula as a column formula:

=MATCH([Auto No]@row, [Auto No]:[Auto No], 0).

3. Put this formula in the highlighted cell:

=SUMIF([Row No]:[Row No], >[Row No]@row, [Amount (excl GST)]:[Amount (excl GST)])

This basically says to sum all the rows in the same column that are higher than the current row and won't sum anything positioned above it.

To tidy it up you can then hide the extra columns. Hope that works?

Bec Sullivan

Hi Jeremy,

Thank you for your help. This does work, but only if I get rid of my total in the same column for the forecasted project costs (which I would like to be forecasted labour costs + other costs - in this example the $124803.15 + $500).

If I add this sum back in, I get a blocked and circular reference error.

Any ideas on how to avoid the errors?

Thanks,

Bec

Bec Sullivan

Hi Jeremy,

Thank you for your help. This does work, but only if I remove the forecasted project costs sum from the same column. I would like to have this be a sum forumla to equal Forecasted Labour Costs + Other Costs ($124,803.15 + $500 in this example).

But when I add this sum forumla in that cell, I get a Blocked and Circular Referecne error:

Any ideas on how to avoid the errors wihout moving the Forecasted Project Costs sum to a different column?

Thanks,

Bec