# Summing of total with dynamics rows coming external datasource

I am using Excel 2010 and retrieving data from SQL analysis service.

When I refresh the data from Excel, the rows all refresh as they are bound to an external datasource. I am adding a separate column with a formula to sum the totals.

With an increment or decrement of these rows, the alignment of custom columns goes out.

How can I resolve this problem that summing of values become dynamic with adding and removal of rows?

-
For this one we need to see your formula for the sum. – Lance Roberts Jun 30 '11 at 5:30
=sum(B1:B7) , in my case the B7 value is dynamic – Gainster Jun 30 '11 at 5:31
OK, I guess I'm misunderstanding precisely what's going on. Good Luck. – Lance Roberts Jun 30 '11 at 5:45
What is an increment or decrement of the rows? – soandos Jun 30 '11 at 5:56

I think I understand you. You are trying to calculate the total of a column but the number of rows may vary.

Firstly, make sure your data is in a proper Excel Table. When you click on a cell in the table, you should get "Table Tools" appears a section on the Ribbon bar.

Add a Total row to the table (Right click somewhere on the table then select Table -> Totals Row). This row will automatically move up/down as the number of rows in your data source changes.

Then add an autocalculated total (you can pick sum, count, average, whatever you need) to the relevant column.

Even though the cell containing the total might change rows, you can always reference it. For example, if your table is called `products` and your column containing the total is called `quantity`, you can refer to the total cell by reference as `products[[#Totals],[quantity]]`

-

=sum(B:B)

(I wanted to just submit the formula to be totally minimalist, but apparently I need more characters, so I added this sentence)

-