Excel formula: Correct syntax of SUM with ROW?

1
votes

The formula =SUM(A2:A(ROW-1)) in the cell B5 for example is not correct, what would be the correct syntax?

I have two columns, A and B, plain positive numbers without decimals. In column A, starting with A2, I have these numbers, let's say A2 is 10, A3 is 20, A4 is 5.

In the cells of column B, I want to have the above formula, in order to sum the values of column A up: B3 would be the "sum" of A2 (10), B4 would be the sum of A2 and A3 (30), B5 would be the sum of A2 and A3 and A4 (35), and so on.

ROW being the current row number, ROW-1 would then be the row number of the previous row, and I always need the sum of all the values in column A, from A2 up to the current row minus 1, hence my above formula but it's an error, and the MS "help" which then opens up is no help for this, couldn't either find anything on the web, with "Excel SUM ROW" and the like.

I tried various other syntaxes, they were all bad, e.g. with [] instead of () around ROW-1, and others, like =SUM(A2:A(ROW()-1)).

What's the correct syntax, please?

1
excel-formulawindows-10
In cell B2 =SUM(A$1:A1) and copy down. This does not work for cell B1. - VBasic2008
Thank you so much, VBasic2008, it was past midnight in Europe yesterday, so I couldn't check at once. Your comment already resolves my initial problem (mix of absolute and relative addresses) because of which I then concocted the non-working "formula", but then teylyn's brilliant explanation of the whole stuff was indeed truly exceptional! Many thanks to BOTH OF YOU! - cuie

1 Answers

2
votes

A few explanations why your formula does not work:

  • a cell address consists of column letters and row numbers, but you can't just combine numbers and letter like that =SUM(A2:A(ROW()-1)).
  • It is possible to construct a range reference as a text string and concatenate the text with the row number, like "A2:A"&Row()-1, but the result is just text. To turn it into a reference that can be used in a formula, you need to wrap it in the Indirect() function. Indirect() should be used sparingly, though, since it is volatile and can cause spreadsheets to become very slow.
  • Excel cell references can use $ signs to define absolute positions. Absolute references don't change when the formula is copied across or down. $A$2 will always point to cell A2.
  • relative cell references without the $ can be used to achieve your goal. To get a better understanding of what the $ signs do, read up on relative and absolute cell references, for example here.

If you start in cell B2 and want to create a sum of all the cells in column A, starting from A2 (because A1 has text), then you can use in B2

=Sum($A$2:A2)

If this formula is entered in row 2, the cell basically says: sum up all the values from A2 down to the cell in column A in my row. (my being from the point of view of the cell)

Copied down, the reference WITH the $ sign will remain as $A$2, whereas the reference WITHOUT the $ sign will adjust to the current row, so in row 5 the formula will read

=Sum($A$2:A5)

This formula will give you a running total up to the current row. If you want the running total to include only values up to the previous row, then you need to start in B3 (that is row 3, not row 2!!) with the formula

=Sum($A$2:A2)

If this formula is entered in row 3, the cell basically says: sum up all the values from A2 down to the cell in column A one row above my row. (my being from the point of view of the cell)

Again, copy that down to apply the running total to the cells below.