find last cell address in Range

[gtd526]

Hello,
Looking to replace the last cell in ($S$10:$S$21) with a formula to find the last occupied cell address in S:S. ie ($S$10:some formula here))
Thank you

The Whole Enchilada.xlsm
	R	S	T	U
9	Stock %'s		Rank
10	2x3x	38.26%	4	fix this formula T10
11	SPDR ETFs	2.97%	9	ie $S$21
12	Technology	42.86%	2
13	Pot'l Invests	38.97%	3
14	NRVUS	0.00%	11
15	ETNA	0.00%	11
16	Retirement	13.12%	7
17	Chaikin	23.39%	5
18	401k	5.68%	8
19	AMTD	0.74%	10
20	Roth IRA	19.70%	6
21	Schwab	57.58%	1
22	Last Occupied Cell (array)
23	"S"&MATCH(2,1/(S:S<>""),1)
24	S21
Main

Cell Formulas
Range	Formula
R10:R21	R10	=LOOKUP(2, 1/((COUNTIF($R$9:R9,$B:$B)=0)*($B:$B<>"")),$B:$B)
S10:S21	S10	=INDEX(C:C,MATCH(R10,B:B,0))
T10	T10	=SUMPRODUCT((S10<=$S$10:$S$21)/COUNTIF($S$10:$S$21,$S$10:$S$21))
T11:T21	T11	=SUMPRODUCT((S11<=$S$10:$S$21)/COUNTIF($S$10:$S$21,$S$10:$S$21))
R24	R24	="S"&MATCH(2,1/(S:S<>""),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rondeondo]

Hi,
A couple of possibilities.
One is offset with count, if you don't have any numbers in column S except for the ones you want to include, replace $S$10:$S$22 with OFFSET(S10,0,0,COUNT(S:S))
Offset can define a range with the 4th and 5th arguements, 5th not used in this example

Another option is to turn your data into a table, the references within a table all for formulas that calculate based on the full data range of the table, so they are dynamic in nature. This would be my choice but I don't know the full extent of your requirements. it may not work for you.

 

[gtd526]

Hi,
A couple of possibilities.
One is offset with count, if you don't have any numbers in column S except for the ones you want to include, replace $S$10:$S$22 with OFFSET(S10,0,0,COUNT(S:S))
Offset can define a range with the 4th and 5th arguements, 5th not used in this example

Another option is to turn your data into a table, the references within a table all for formulas that calculate based on the full data range of the table, so they are dynamic in nature. This would be my choice but I don't know the full extent of your requirements. it may not work for you.

Thank you for the formula.

 

[Peter_SSs]

One is offset with count, if you don't have any numbers in column S except for the ones you want to include

.. and also provided there are no blanks or text values in the range you want to use - see U10 below where an incorrect result is returned.

In any case I would consider avoiding the volatile function OFFSET especially if there are a lot of these sort of calculations in the worksheet - suggestion in V10
However, using whole column references in formulas like that carry a considerable calculation burden. If you can give some sort of row limit like i have in W10, the calculation burden is much less.

23 08 14.xlsm
	S	T	U	V	W
10	3		10	27	27
11	2
12
13	5
14	xx
15	9
16	8
17
Test

Cell Formulas
Range	Formula
U10	U10	=SUM(OFFSET(S10,0,0,COUNT(S:S)))
V10	V10	=SUM(S10:INDEX(S:S,AGGREGATE(14,6,ROW(S:S)/(S:S<>""),1)))
W10	W10	=SUM(S10:INDEX(S:S,AGGREGATE(14,6,ROW(S10:S1000)/(S10:S1000<>""),1)))