How to express cell id using string letter and current row number in an Excel formula?

Question

I want to calculate the sum on a column and then subtract sum on another column BUT using only the values from a given row to the current row (the one in which formula resides).

So, in an "informal custom language", I would need something like this:

Suppose I am in C5: =(sum(A1:"A"+ROW())-sum(B1:"B"+ROW()))

How can I write a correct expression in Excel for this?

Answer 1

You can try using INDIRECT, which accepts a string reference to a range and returns the range itself:

=SUM(INDIRECT("A1:A"&ROW()))-SUM(INDIRECT("B1:B"&ROW()))

Here, we start with a 'stub' of "A1:A". We then get the current row with ROW() (so 5 in this example) and concatenate it with our stub, giving us INDIRECT("A1:A5"). Since INDIRECT will return the range referenced by its argument ("A1:A5" here), we can wrap it with the SUM formula to get the result (which is identical to SUM(A1:A5)). We then do the same thing for column B.

Answer 2

I think you may be looking at it backwards. You need to anchor the first cell reference in the call to SUM to the first row, but let the second cell reference change with the row. Try this in cell C1:

=SUM(A$1:A1) - SUM(B$1:B1)

Now when you copy that down the column, it becomes:

C2: =SUM(A$1:A2) - SUM(B$1:B2)
C3: =SUM(A$1:A3) - SUM(B$1:B3)
C4: =SUM(A$1:A4) - SUM(B$1:B4)
C5: =SUM(A$1:A5) - SUM(B$1:B5)