SOLVED

sum row cells to another cell in same column

New Contributor

Sorry, I can't believe this is not already answered somewhere, but I can't find it.

 

I want the value in column E of any particular row to always be the sum of the values in columns C and D from the same row. I understand that I can do this on a row-by-row basis by manually entering a formula, e.g., =SUM(C2:D2) for row 2. I really don't want to have to type this formula for every bloody row in the column, and then if I do some editing which changes row numbers the formulas are all wrong. Isn't there some magic shorthand that means "this row" that I can use for every row? Or better yet, something that I only have to type once for the entire spreadsheet for all time, perhaps in the column header, and then it works for every new row which I add to the bottom of the table? Suppose this magic shorthand for "this row" is the character # . I would like to use a formula that looks like =SUM(C#:D#).

 

How do I do this? I'm sure there must be a way.

5 Replies

@rbd7436 

You may use structured tables instead of ranges. It operates with "this row" and automatically expands the formula on all rows in the table.

With range, you may enter formula into the first cell of the column, select starting from that cell till end of the column and Ctrl+D

Thanks, but what is the formula that I need to enter in that first cell? I still don't know the magic character for "this row" (if there is such a thing).
best response confirmed by Hans Vogelaar (MVP)
Solution

@rbd7436 

For the structured table it's enough to enter formula like

image.png

in any row, it will be applied to all other rows (of course, if they were empty). Not necessary to type everything what is in formula bar, with using of arrow keys Excel helps to combine formula. "@" means "this row" Using structured references with Excel tables (microsoft.com)

 

For the range enter formula in first cell and select from it  end of the column

image.png

Ctrl+D

image.png

Thanks for your help, @Sergei Baklan !

@rbd7436 , you are welcome