Excel formula Correct syntax of SUM with ROW

0 votes
What would be the proper syntax if the formula =SUM(A2:A(ROW-1)) in cell B5 is incorrect?

I have two columns of positive values with no decimals, A and B. I have these values in column A, starting with A2. Let's say A2 is 10, A3 is 20, and A4 is 5.

I want to use the following formula in the cells of column B to add the values from column A together: 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.  

I always need to add up all the values in column A, from A2 up to the current row minus 1, which is why I created the above formula, but it is incorrect. The MS "help" that then opens up is also of no assistance, and I was unable to find any information online about "Excel SUM ROW" or similar functions.

I tried several different syntaxes, but they were all awful, such as =SUM(A2:A(ROW()-1)) and using [] in place of () around ROW-1.

Please let me know the correct syntax.
Nov 10, 2022 in Others by Kithuzzz
• 27,160 points
49 views

1 answer to this question.

0 votes

A few explanations of why your formula does not work:

  • A cell address consists of column letters and row numbers, but you can't just combine numbers and letters 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.

I hope this helps you.  

answered Nov 10, 2022 by narikkadan
• 50,080 points

Related Questions In Others

0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18, 2022 in Others by Edureka
• 13,640 points
103 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 50,080 points
105 views
0 votes
1 answer
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 50,080 points
90 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
203 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
357 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
165 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
143 views
0 votes
1 answer

How to sum the value of 2 rows with vlookup by only using 1 formula?

 Try in Excel Online: • Formula used in cell C3 =SUM(SCAN(0,M3:N3,LAMBDA(x,y,VLOOKUP(y,P3:Q12,2,0)))) Works ...READ MORE

answered Jan 17 in Others by narikkadan
• 50,080 points
47 views
0 votes
1 answer

Excel OFFSET function with a dynamic row argument

The answer is to make "anchor cells" ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 50,080 points
154 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP