Create a reference to a whole column in Excel through a formula

Solution 1:

To just clarify Mureinik's answer a little bit, because it's a good start, for column MyColumn you could use: OFFSET($A$1,0,MyColumn-1,ROWS($A:$A),1)

Start at $A$1, and then offset by 0 rows and (your column number minus 1), using a height of ROWS($A:$A) and a width of 1. Max rows in recent versions is 1,048,576. You can use a smaller height if you know what the maximum height is likely to be.

So to sum column 3:

=SUM(OFFSET($A$1,0,3-1,ROWS($A:$A),1))

The answer from sgp667 is not bad, but note that SUBSTITUTE could be a little messy and slow if you are using it a lot.

To reference another sheet, just change the reference to $A$1, so for instance: SUM(OFFSET(Sheet2!$A$1,0,3-1,ROWS($A:$A),1))

Solution 2:

Number of cells in a column is a constant, it is 1048576 since Excel 2007 and it was 65535 if in earlier versions I recall correctly. If you open xls (old format) files it falls back to the old rows count, so you might want to account for that.

So you might reference the range that starts with row 1 and ends with row 104576 like A1:A1048576, but using whatever formulas you use.

However, referencing whole columns might make your spreadsheet very slow. Usually it's a good idea to count rows (using COUNTROWS once or anything else) and reference the range exactly.