Formula with non fixed row number

[shardin]

Hi,
I wonder if this is possible.

For instance I have a formula such as =sum(A1:A9). But imagine that the range is not always till row 9 but depends on the total number of rows that are in the table. How can I change that 9 so that it takes a number equal to the total rows in the table?

N.B.: Please note that the final formula I want is not that simple one. It is actually =INDEX($F$1:$F$1047,MATCH(1,INDEX(($G$1:$G$1047=G2)*($L$1:$L$1047<>"47200")*($L$1:$L$1047<>"47700"),0),0)). I want to change 1047 by the relevant number of rows. Also, I do know how to do it in VBA, but I have a problem with the lenght of the formula there (not that one, but other bigger than that)

[Pepe Le Mokko]

You could use Dynamic ranges for example

[shardin]

Names... Yes, but, I am not really very fond of names. Is there any other way?

[Haseeb Avarakkan]

Hello Shardin,

You can apply those formulas directly in formula. Assuming column G is always be text entries.

=INDEX($F$1:$F$100000,MATCH(1,INDEX(($G$1:INDEX($G:$G,MATCH("ZZZZZ",$G:$G))=G2)*($L$1:INDEX($L:$L,MATCH("ZZZZZ",$G:$G))<>"47200")*($L$1:INDEX($L:$L,MATCH("ZZZZZ",$G:$G))<>"47700"),0),0))

[FDibbins]

I put this together for another member, maybe you can adapt it to what you want? If you have a table of data like this...

	F
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22

Take this approach...

	B	C	D
1		Start	Stop
2		5	12
3
4		OFFSET	INDIRECT
5	SUM	68	6800
6	COUNTIF >8	5	5
7	COUNTIF =8	1	1
8	MAX	12	12

C2:D2 are the start and stop rows (more on that later)
C5=SUM(OFFSET($F$1,$C$2-1,0,D2-C2+1,1))
C6=COUNTIF(OFFSET($F$1,$C$2-1,0,D2-C2+1,1),">=8")
C7=COUNTIF(OFFSET($F$1,$C$2-1,0,D2-C2+1,1),"8")
C8=MAX(OFFSET($F$1,$C$2-1,0,D2-C2+1,1))

Alternatively...
D5=SUM(INDIRECT("H"&$C$2&":H"&$D$2))
D6=COUNTIF(INDIRECT("F"&$C$2&":F"&$D$2),">=8")
D7=COUNTIF(INDIRECT("F"&$C$2&":F"&$D$2),8)
D8=MAX(INDIRECT("F"&$C$2&":F"&$D$2))

Now, in D2, you could have something like a counta() function that will count the number of entries (minus any rows for headings etc)

[shardin]

Yes! The INDIRECT way is what I was looking for.
Thanks Ford, and all.