6

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.

5

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.

1

Here is an easy way:

=SUM(A:A)-SUM(A1:A5)
  • 3
    Does not work for non-numeric values. – Argyll Nov 8 '15 at 14:51
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.

  • 1
    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
0

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.