CODEWITHSUNDEEP
exceldynamicsum
HeadSpaceDude
HeadSpaceDude

Reputation: 1

setting a dynamic cell reference in excel

I have a sheet setup that calculates totals. Easy enough if the data is already there but not if adding new data. So what I would like to be able to do is to not specify a specific end cell for the sum formula but let it update as more columns are added.

How can I do this with =SUM(m4:m?)

Upvotes: 0

Views: 2493

Answers (4)

Kroz
Kroz

Reputation: 11

Suppose you need totals for data in M4:M10.

To make just open-ended range, you can make lower limit "too far": =SUM(M4:M100000).

Alternatively you can make it as =SUM(M:M) - SUM(M1:M3)

But this is not applicable when you need to have totals value just below the set of values. In this case you have 2 ways.

Using Excel embedded features

The formula will look like his: =SUM(M4:M10). If you insert a new row between M4 and M10 (for instance, select row 5, right-click, insert row), you formula will be automatically adjusted to =SUM(M4:M10).

The problem may happen if you want to insert a new value above the first row (select row 4, right-click, insert row) or below the last row (select row 11, right-click, insert row). In these cases totals formula will not be adjusted.

Possible workarounds:

  • For the "above first row" issue, I prefer to make some empty row above and hide it. In our case I would hide row 3 and make totals formula look like =SUM(M3:M10), so, when you insert a new row above the first row, in fact you insert a row to the middle of the table, and totals formula will be adjusted.

  • For the "below last row" - leave empty row below; but in this case you cannot hide it; just make it different color and make some remark like "new values shall be inserted ABOVE this line".

INDEX()

Interesting trick is using INDEX() function, which returns a reference to a cell in the array. For our case, the array can be the whole M row and, the index - row number.

  • For the "above first row" issue make totals formula like this =SUM(INDEX(M:M;4):M10). So, calculation will always start at row 4, even if some lines will be added/deleted.

  • "below last row". Suppose you have your "totals cell" in M13 and you want to have totals for all value between M4 and the "totals cell". The formula may look like =SUM(M4:INDEX(M:M;ROW(M13))) or, considering "above first row" case: =SUM(INDEX(M:M;4):INDEX(M:M;ROW(M13)))

Hope this helps

Upvotes: 1

thanos.a
thanos.a

Reputation: 2694

You can use a counta to find the max row number. Then pushing that into an indirect will give you the range you need.

=SUM(INDIRECT("A1:A" & COUNTA(A1:A1000000);TRUE))

Assumptions:

  • Data are on column A
  • Data start from first row
  • There are no blanks rows

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

You can compute the last row containing a number using this formula:

=LOOKUP(2,1/ISNUMBER($J:$J),ROW($J:$J))

This formula would not have a problem if you had text or blanks in the range.

You could then define that formula as a Defined Name

enter image description here

and use the formula:

=SUM(OFFSET(J4,0,0,LastRow-3))

to Sum the range. Note the -3 at the end to compensate for the first cell being in row 4.

Another option would be to just set your range to a fixed range that you can guarantee will be larger than any range you might actually use:

=SUM(J4:J1000)

Upvotes: 0

PaulG
PaulG

Reputation: 1199

Sum(m4:m?) insinuates that you are looking to add more rows as opposed to adding column data. If you want to auto sum a row data you can use something like:

=SUM(OFFSET(A1;0;0;COUNT(A:A);1))

However this assumes that the data is contiguous in each cell and also empties are not allowed for 0 because it gets the count wrong.

However: You could also define a table for the data range. If you add data to columns/rows that are in that data range, they will be included in the adjusted formula automatically - very nice indeed.

Select your data range, then Select Insert:Table. This will give your table a name like Table1. Your sum function would now be adjusted to look something like: =SUM(Table1)

Now, as you add to the range, the table resizes, and your function just works. The beauty of using a table, is that if you add data to the row/column immediately after the table it resizes and includes that range. This is hard to do without a table. You can also change the format of the table, or make the format colours invisible but you're probably better off with some format to show the data area of the table to the user.

Upvotes: 0

Related Questions