# Formula to sum a range of rows and copy the sums vertically downwards in a column?

I need to display the sums of rows vertically using ctr+drag for over 80 rows downwards in one column to replicate the formula for the remaining 80 rows, but excel only continues to modify the formala if I drag horizontally.

Here is a small table to demonstrate what I am trying to do.

In cell A12 is the sum of rows A1:F1. I want to be able to display the sums of A2:F2 in cell A13, and A3:F3 in cell A14. At the moment I can only copy as value, and then transpose. But I don´t want to do this 80 times weekly when I receive this table.

Which formula would enable me to quickly calculate the sums of rows but display them vertically?

• You can compute in columns, and transpose? – hjpotter92 Mar 13 '18 at 14:58
• It's not clear to me what you're trying to do. Could you show what your result is supposed to be? – Bandersnatch Mar 13 '18 at 15:02

What you want is to enter an array formula for transposition. For this, select the destination column, e.g. `A14:A:19`, enter the `=TRANSPOSE(A12:F12)` command from the original row, and press CTRLSHIFTENTER.
What you want to achieve is thus easy in two steps: first sum into a new row `A12:F12`, then tranpose to a column.
``````=SUM(INDIRECT("A"&COLUMN()):INDIRECT("F"&COLUMN()))