1

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?

Gary's Student
  • 19,396
Root Loop
  • 955

2 Answers2

2

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.

Mike Honey
  • 2,562
0

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

wbeard52
  • 3,447