## Issue

how can the range selection be automated in general in excel formula.

As an example, if I am using a SUM formula somewhere in the sheet (not below the range) as `=SUM(A1:A10)`

. And if the values are now updated in further cells, say till A15, I would have to manually update the formula to `=SUM(A1:A15)`

. And so on.

How can the above formula be automated to select the range till the last row that contains the value.

Pardon me if this is repeated or silly. I couldn’t find anything specific as this.

## Solution

You are looking for a so-called dynamic range. You could use `SUM(A1:INDEX($A:$A,MATCH(2,1/($A:$A<>"")),1))`

.

Also possible: `SUM(A1:INDEX($A:$A,COUNTA($A:$A),1))`

, provided that you make sure *not* to leave cells blank within the specified range. The first one also works with blanks in between.

To set a max on the range, change `$A:$A`

into something like `$A$1:$A$50`

.

Update: explanation formulas. `INDEX(rng, row, col)`

is used here with col = 1 (first col of the range; we could have left this out) and a dynamic row ref, to create a dynamic range like `A1:A" & last_row`

. `COUNTA($A:$A)`

will get us the correct row if your values are filled in without blanks). E.g. below, in col A, it can be used, but in col B we get the wrong row (we would want to have 5 here, not 4):

Using `MATCH(2,1/($A1:$A5<>""))`

solves such a problem. Here, `($A1:$A5<>"")`

will resolve to `{TRUE;TRUE;TRUE;FALSE;TRUE}`

. Next, `1/{array}`

will resolve to `{1;1;1;#DIV/0!;1}`

(since `TRUE = 1`

, `FALSE = 0`

). We are trying to match `2`

, which cannot exist (hence, it could be any val `> 1`

). We are implicitly using `match_type = 1`

(the default), which in case of no exact match will return the position of the last val in the array that is *below* 2, i.e. for col B it will return `5`

in ref to the fourth and last `1`

in the array.

Answered By – ouroboros1

Answer Checked By – Mildred Charles (AngularFixing Admin)