1

Let's say every day (1 day = 1 row) a number is being added in column A I want to have column B show the total of these numbers. However, if I drag it down, it keeps moving 'B1' too, while I only want the second one to move +1 -->. So:

Cell B1: '=A1' 
Cell B2: '=SUM(A1:A2)'
Cell B3: '=SUM(A1:A3)'
Cell B4: '=SUM(A1:A4)'

etcetera. Dragging this down doesn't give the wanted result.

5 Answers 5

0

You don't do this by increasing the formula by a cell for each row. This is not possible.

Instead, you use as the formula

cell B1 = A1
cell B2 = SUM(B1, A2)
cell B3 = SUM(B2, A3) 

Now you can drag down the formula from B2 and B3 (don't include B1 in the selection!) for as many rows as you like.

2

Well, in that case ...

Use the following formula in B2:

=SUM(A$1:A2)

The $ sign is meant to fix the row. If you it drag horizontally, I will follow the column letters.

See answer given, here on Web Applications: https://webapps.stackexchange.com/a/27869/29140

1

Since OP wants a result even in B1 I suggest a small variation on @Jacob Jan Tuinstra's A, in B1 and copied down to suit:

=sum(A$1:A1)
0

Just for the sake of completeness ...

Insert in cell B1:

=SUM(OFFSET($A$1,0,0,ROW(A1)))

Then, just copy or drag it down.

Note that the answer by rumtscho is much more efficient, though.

-1

Use the dollar sign before the thing you don't want to change:

B2 = SUM(A2:A$1)

Now, when you drag this down, it becomes, say:

B10 = SUM(B10:A$1).

The "1" in A1 remains as it is because it was prefixed by the dollar sign.

Let me know if this doesn't work.

1
  • That does not work, B10=SUM(A10:A$1) but B10<>SUM(B10:A$1) also seems to me once corrected a duplicate of @Jacob Jan Tuinstra's answer posted 2 years earlier (albeit with the range flipped).
    – Blindspots
    Jul 12, 2023 at 1:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.