Forum Discussion
INRIM
Dec 27, 2020Copper Contributor
Adrees to the column/row end
I would like to form an address from a cell to the end of its column or row, for instance in a total on the top of a column whose extension covers the column below in full. This is different from a f...
Riny_van_Eekelen
Dec 27, 2020Platinum Contributor
INRIM As a variant, and since you give an example of adding a total in the top of the column, I assume you are working with numbers. The following formula will sum the range from A5 to the last cell with a number.
=SUM($A$5:INDEX(A:A,MATCH(99^99,A:A)))
Similarly, if you want to sum e.g. from D5 to the last number of row 5, use this one:
=SUM($D$5:INDEX(5:5,MATCH(99^99,5:5)))
Both will allow the range to include blanks.
- SergeiBaklanDec 28, 2020Diamond Contributor
Alternatively for the last
=XMATCH(,A:A)-1
could be any values in the cells, assuming there are no blanks in between