# 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.

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.

How about `=Sum(\$A6:A...`
The dollar sign makes it an absolute reference to the cell so if you copied it to B the columns 1-5 wouldn't be touched. Sorry for the typo.

• Why is dollar sign needed again? Would that make it `sum(\$A6:B...` when applying to column B? – Argyll Nov 8 '15 at 14:52
• I fixed typo. I meant 1-5 Rows 1 thru 5. So the Dollar Sign says only apply the function to row six. – Noshad Chaudhry Nov 10 '15 at 21:29
• How does \$A6 apply to 6 rather than A? Regardless, have you actually tested this? At least in my version of Excel, this range specification produces an error. – fixer1234 Nov 11 '15 at 3:09
• Yeah \$A a applies to A. \$A\$6 applies to A6. Sorry for the confusion. I'll test and let you know if solved. – Noshad Chaudhry Nov 11 '15 at 4:46

Here is an easy way:

``````=SUM(A:A)-SUM(A1:A5)
``````
• Does not work for non-numeric values. – Argyll Nov 8 '15 at 14:51

My workaround has been to use `(A5:A\$1048576)`. Since the maximum number of rows in Excel is 1048576, this has the desired effect. The `\$` is so placed to ensure the array doesn't break if I copy the formula to the next cell down.

• On one hand, you have to type that number. On the other, this is massively inefficient. `A:A` already doesnt have auto detection of non-blank cells in a sparse sheet and instead naively check several thousand rows unless existing max row is higher, in which case I believe it proceeds to check the next several thousand. Forcing a check blank on 1 mil cell for every single instance should result in your sheet practically crashing. – Argyll Jul 14 at 5:07