pen with math notes

Excel – Total at the Top

It’s that time of year again. Tax time. Now that I’ve made you sad, lets check out a formula which is very useful when adding up long columns of numbers (deductions anyone?)

Column of numbers, with accompanying total
Put the sum of the column at the TOP of the page, making it easy to find

When you have long columns of numbers of irregular length, it is often easier to have the totals display at the TOP of the page. That way, you don’t have to scroll all over the place to find them. The other advantage of this formula is that you can use it to keep an eye on your total as it accumulates, since you don’t have to know how many rows long the column will be.

The formula here is =SUM(OFFSET(A1,1,0,COUNT($A:$A),1))

Where the OFFSET & COUNT functions are used to create the range that will be summed.

The syntax for OFFSET
OFFSET(reference, rows, cols, [height], [width])
OFFSET(A1,1,0,COUNT($A:$A),1)

  • A1 is the reference cell,
  • Rows – the range begins 1 row down from A1,
  • Cols – the range begins in the same column 0,
  • Height – the number of rows to be included are counted using the COUNT function, COUNT($A:$A) counts the cells in Column A with numbers in them
  • Width – is set to 1 column (column A) wide

I have to say there is something very satisfying about seeing that total increase as each number is entered in the column.

featured image by
Antoine Dautry

Leave a Reply

Your email address will not be published. Required fields are marked *