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.

CodePudding user response：

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.