Sum a fixed range

[Bagsy]

Hopefully somebody can help me out a bit here.
I have a range I need to sum =SUM(F5:F3000)
But I need to allow the users to be able to delete rows within this range once they are completed, this then has the effect of changing the formula even if it absolute =SUM(F$5:F$3000)
How do I fix this formula so that the range is always (F5:F3000) even if rows within this range are deleted.
I cannot use VBA in this case to re-enter the formula, as this needs to be emailed and cannot have any macros.
Thanks in advance
Gary

 

[AlanY]

try

=SUM(OFFSET($F$1,4,0,2996,1))

 

[Bagsy]

That’s is brilliant thanks Alan
Would you mind explaining what the formula is doing and how it works please

 

[AlanY]

the formula rely on the offset() function to define the array to sum up.

the offset() in the formula basically say set up an array from Cell $F$1, start from 4 rows down and 0 column across (i.e. $F$5), with 2996 cells (i.e. to $F$3000).

 

[Bagsy]

So easy when you know
Thanks for your time
Gary

 

[AlanY]

you're welcome

 

[Bagsy]

Appologies Alan
What is the 1 for after the 2996 =SUM(OFFSET($F$1,4,0,2996,1))
I have tried changing it to see what would happen and it diddn't seem to make any difference unless I change it to 0 then it gives me an error

 

[FormR]

How do I fix this formula so that the range is always (F5:F3000) even if rows within this range are deleted.

Hi, here is another option that avoids the volatile OFFSET() or INDIRECT() functions.

=SUM(INDEX(F:F,5):INDEX(F:F,3000))

 

[AlanY]

Appologies Alan
What is the 1 for after the 2996 =SUM(OFFSET($F$1,4,0,2996,1))
I have tried changing it to see what would happen and it diddn't seem to make any difference unless I change it to 0 then it gives me an error

1 is the width of the array, as your data is in single column thus 1.
if you have data in the corresponding column G, and change the 1 to 2 then you can see the difference

Book1
	A	B	C	D
1	1	6		15
2	2	7		55
3	3	8
4	4	9
5	5	10
Sheet1

Cell Formulas
Range	Formula
D1	=SUM(OFFSET($A$1,0,0,5,1))
D2	=SUM(OFFSET($A$1,0,0,5,2))

 

[AlanY]

you can always check the syntax of the formula by click on the formula bar like this

<img src="https://www.pixelsbin.com/images/2019/06/20/Untitled4fa2a03ff8dd5b0d.jpg" alt="Untitled4fa2a03ff8dd5b0d.jpg" border="0">