CODEWITHSUNDEEP
excelsumformulacircular-reference
FernandoSBS
FernandoSBS

Reputation: 655

How to autosum a full column in excel without circular reference?

enter image description here

I would like to have in the red circle cell (C19) the sum of all the C column. I've tried using C1 to C50 in the formula (=SUM(C1:C50)). I use C1:C50 and not C1:C18 because if I add some lines before the final sum I would like those to be included in the final sum.

So 2 questions:

1) How can sum all the C column without only C1 (which is a date) and without C19 (to not cause the circular reference, since that is the cell where just the previous values should be sum and presented?

2) If there's a formula for that, would it auto-update if I add more lines (let's say I add another line and the final sum changes to C20)? Then C19 should also be add to the sum.

PS: the image is showing the formula for the B column because I will use the same formula in all columns, I just asked about C because there I have a working sum.

Upvotes: 1

Views: 13113

Answers (4)

Allen Holman
Allen Holman

Reputation: 125

I like to do the following:

  • In a field in the row that I want to skip(i.e. A1), enter text that uniquely describes the row as the header/skip row (i.e. 'header-skip')
  • In the column that I want to sum (i.e. B:B) without a circular reference, enter a "SUMIF" formula using the header text as part of the IF to skip. =SUMIF(A:A, "<>header-skip", B:B)

For example

[   ][      A      ][                 B                 ]
[ 1 ][ header-skip ][ =SUMIF(A:A, "<>header-skip", B:B) ]
[ 2 ][             ][                9.5                ]
[ 3 ][             ][                7.5                ]
[ 4 ][             ][               25.0                ]

Your Formula on line 1 should now correctly show "42" without any circular reference. This is easier for me to remember and faster for me to put in a sheet than using offsets, indexes, and indirects, etc.

Hope this helps someone!

Upvotes: 4

Ty of Vegas
Ty of Vegas

Reputation: 1

Dunno if you're still interested but: There is a work-around that will work and not give you a circular reference.

Here's an example from my own spreadsheet: "=SUM(M1:OFFSET(M13,-1,0,1,1))"

Normally you'd sum a set of data with "=SUM(M1:M19)".

But if you want to do that AND still be able to add rows you need to incorporate an OFFSET formula. OFFSET locates your reference cell and then moves up or down the number of cells you tell it to.

For example mine (above) finds M13 then moves UP one cell (-1) and the result is 1 cell tall by one cell wide (the 1,1).

Upvotes: 0

Zairja
Zairja

Reputation: 1439

If you use =SUBTOTAL(9, range) then the totals calculated don't go toward later subtotals. In other words, if you have a subtotal in C19 (or other cells in Column C), then your subtotal at the end (e.g. C50) will ignore subtotals in that range (=SUBTOTAL(9,C2:C49)). In this way, you don't have to worry about omitting certain cell references from the range you wish to sum.

Upvotes: 3

Conrad Frix
Conrad Frix

Reputation: 52645

1) How can sum all the C column without only C1 (which is a date) and without C19 (to not cause the circular reference, since that is the cell where just the previous values should be sum and presented?

=SUM(C2:C18,C20:C50)

2) If there's a formula for that, would it auto-update if I add more lines (let's say I add another line and the final sum changes to C20)? Then C19 should also be add to the sum

If you were to insert a row between rows 2 and 18 with the above formula Excel will automatically change it to =SUM(C2:C19,C21:C51)

Upvotes: 2

Related Questions