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.

3

4 Answers

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.

2

Here is an easy way:

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

1

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.

1

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.

4

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy