In a previous post I explained how to add up an entire column in Excel using a simple formula, but what if you want to sum an entire column without the first row or header?
In this post I’ll explain 3 ways to do it with Excel formulas – no VBA required.
Method 1: Using the Index Function
The best way to sum an entire column without the first row is with the following formula:
This formula looks complex but it is by far the best option. Let’s break it down to see what it does, starting with the innermost function.
ROWS(A:A) returns the number of rows in all of column A, which is incidentally also the row number of the last cell in column A.
We can now use this row number inside the INDEX function to return a reference to the last cell in column A. You might be aware that the INDEX function returns a value at the intersection of a specified row and column within a certain range, but few people know that it can also return the reference of the cell at that particular intersection, instead of the value. This latter form takes the following arguments:
- reference is the cell range in which to look.
- row_num is the row number within reference from which to return a reference.
- column_num is the column number within reference from which to return a reference (optional).
- area_num is a selector to select a particular reference if more than one is supplied in the first argument (optional).
For our purposes, only the first two parameters are needed. So in the formula above, INDEX looks within column A (specified by A:A) and returns the reference to the cell in the last row of the column, as produced by the ROWS function. This effectively returns a cell reference to the last cell in column A.
Finally, we combine cell A2 with the last cell in column A using a colon operator to create a cell range over which to sum. The SUM function thus ads up all cells from A2 to the last cell in column A, thus effectively summing the entire column except the first row or header.
Summing a Different Column
You could use the formula to sum any column. Simply change all A’s in the formula to the column you want to add up. For instance, to add up the entire column D except the first row, use the formula:
Summing a Column with a Header of Several Rows
This formula works just as well if your header contains more than one row. Simply change the A2 in the formula above to the first row below your header.
For instance, if your header is from row 1 to row 6 and the data you want to sum begins in row 7, change the A2 to A7:
Method 2: Hard-coding a Formula
A second solution would be to simply hard-code the entire range into a formula. For Excel 2007 and newer this would be:
And for versions before Excel 2007 the formula would be:
This will serve well for the purposes of what we’re doing here, but poses two problems:
Firstly, according to this post all versions of Excel from 2007 onward have a total of 1,048,576 rows, while any version before this has only 65,536 rows. So when you create a spreadsheet in a post-2007 version of Excel and try to open it in a pre-2007 version, this formula will produce a #NAME error. Likewise, when you open a pre-2007 spreadsheet in a post-2007 version of Excel, the formula will only add up to row 65,536.
Secondly, it’s difficult to remember large numbers like these, and therefore you’ll need to look up the exact number every time you create such a formula.
For these reasons, I suggest rather using the formula in Method 1.
Method 3: Subtracting Unwanted Cells
A third method would be to add up the entire column and then subtract any unwanted cells from the total:
Although this formula is the simplest of the methods listed here, it produces errors when the header contains text.
If cell A1 contains a number the formula will work as expected, but if it contains text a #VALUE error will be produced.
I’ve presented 3 options here for adding up an entire column except the first row or header. Method 1 is by far the most versatile, but is also the most complex to remember. The other two methods will work well in certain cases, but not in all.
Do you know another way to do this? Please let me know in the comments below.