# Automate range selection in excel formula

## 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.