How can I sum the whole column without freeze head?

I have a situation: I need to SUM each column, but the total rows in each column will keep changing. users are keep adding data in each columns, so I only know the start of the column is A3, but i dont know where it ends. I want to setup A2 as SUM for column A, so no matter how many data user put into column A it will auto sum them all.

I was trying to use =sum(a:a) but i can't have head in there...

Any good idea?

  • 2
  • I checked that post and tried their solutions, all I got is [#NAME?] so I posted a similar question... – Root Loop Apr 22 '14 at 16:55
  • Can you post an example of your column from Excel? – summea Apr 22 '14 at 17:04
  • i tried =sum(a$3:a) or (a:a) as another post suggested, it doesnot work. well, now i use the stupidest way to solve this lol =sum(A3:A1000000), I am sure the rows wont be over than 1 million...stupid but easy lol – Root Loop Apr 22 '14 at 17:25
  • Are there any blanks in column A, or is there a value for each record? – Excellll Apr 22 '14 at 19:49

I would use a dynamically named range. Let's assume that all of the data to be summed is in column A. Cell B1 is the total sum for that column. Use the following steps to create a dynamic range that will expand and contract with your data. The dynamically named ranges are great because they only make Excel calculate the cells with numbers in them making your formula much faster than before.

  1. Select cell B1
  2. Define a new name in the name manager. Open Define Name dialog box
  3. Create a name for the dynamic range - ColumnARange
  4. Insert the following formula in the refers to: textbox. =OFFSET($A1,2,0,COUNTA(A:A), 1)
  5. Click OK
  6. In Cell B1 type the following formula =SUM(ColumnARange)

The named range will dynamicaly expand and contract with numeric cells. It will stop if any cells contain only alpha characters or is blank. See this blog for more information.

Ozgrid Dynamic Named Range


I would first convert the range to a table (Insert Ribbon, Table).

Then I would go to the Table Tools / Design ribbon and check the option for a Total Row. This will appear at the bottom, and by default will generate the formula =SUBTOTAL(109,[My Column Name]) , which produces a Sum.

If you must have your formula in cell A2 (outside the table), then I would use this formula:

=SUBTOTAL(109,Table1[My Column Name])

You can then insert and delete rows from the table without needing to edit either formula.

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.