29

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.

Argyll
  • 616

4 Answers4

23

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.

Máté Juhász
  • 21,451
  • Could you please provide a similar example of use INDIRECT function that returns the entire row starting at some particular column - e.g. the row 7 starting at the column C ? Thanks – Ωmega Δ May 04 '20 at 20:35
  • I'd use offset for that. It's much more complicated with indirect. – Máté Juhász May 05 '20 at 03:11
  • Really? It seems like a concise range notation should be available for such a common case. Many spreadsheets have rows with headers. There are many other conventions in excel that acknowledge this. Why not "A5:A". This seems a logical extension of "A:A", meaning "all non-tempty cells in column A". How 'bout "A5:"? How bout a symbol that means "the theoretical end to non-empty data in A", like "$" (borrowed from regular extension line range notation)? Now "A5:A$" has the right semantics and is easily typed and remembered. OK. Microsoft flame-off. – cycollins Nov 15 '23 at 23:03
4

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.

foolishgrunt
  • 75
  • 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 '19 at 05:07
3

Here is an easy way:

=SUM(A:A)-SUM(A1:A5)
Gary's Student
  • 19,396
-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.

Máté Juhász
  • 21,451
Noshad Chaudhry
  • 134
  • 1
    Why is dollar sign needed again? Would that make it sum($A6:B... when applying to column B? – Argyll Nov 08 '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
  • 1
    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 03: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 04:46