How to include an entire column starting at a particular row in a formula in Excel?

I am looking to do summary statistics on a column of varying lengths.

I want to do something like =sum(A:A) except I want to avoid the first 5 rows of column A. Is there a way to do it?

EDIT: Someone pointed out a possible duplicate. Except neither of the solutions in that question actually works. If someone is proposing to apply ISBLANK() to the whole column until the maximum index, I would like to see clarification on the different between that and formats such as A:A -- in terms of speed.


Solution 1:

You can use either OFFSET, either INDIRECT to refer the custom range:

  • OFFSET(A5,0,0,ROWS(A:A)-ROW(A5)+1)
  • INDIRECT("A5:A"&ROWS(A:A))

Both result in a range from A5 to the bottom of the column.

Solution 2:

Here is an easy way:

=SUM(A:A)-SUM(A1:A5)