Sum column to last row with data

[Shadkng]

Hi, can someone provide a formula to sum a column to the last row with data? I would like to sum column L from cell L3 down to the last cell with data. I have been using a regular sum formula but when I delete rows my range keeps getting smaller. Thanks for the help

 

[jtakw]

Hi,

If it's a simple SUM

=SUM(L:L)

 

[Shadkng]

Hi,

If it's a simple SUM

=SUM(L:L)

Yes, but I have to begin at L3

 

[Siddharth Rout]

Is this what you are trying?

=IFERROR(SUM(INDIRECT("L3:L" & MAX((L:L<>"")*(ROW(L:L))))),0)

Explanation:

MAX((L:L<>"")*(ROW(L:L)))

will give the row number of the last non blank cell in that column.

"L3:L"&MAX((L:L<>"")*(ROW(L:L)))

will give something like L3:L(LastRow)

and INDIRECT() will convert the above string to cell address and SUM() will use that cell address to calculate sum.

We need IFERROR() to control any error or if Column L is empty.

 

[jtakw]

Yes, but I have to begin at L3

What's the reason?
SUM ignores Text and Blanks, unless you have numbers in L1:L2 you don't want included, then, just this:

=SUM(L:L)-SUM(L1:L2)

Book3.xlsx
	L	M	N	O
1	Header		100
2	something	10	1000	10
3	1		1
4	2		2
5
6
7	3		3
8
9	ABC		ABC
10
11	4		4
12
Sheet1017

Cell Formulas
Range	Formula
M2	M2	=SUM(L:L)
O2	O2	=SUM(N:N)-SUM(N1:N2)
L6,N6	L6	=""

 

[Shadkng]

Thanks guys. One of these should work fine.