Sum of two values including one generated by formula

[ChristophC]

This is a very basic question best asked by presenting two scenarios:

SCENARIO 1
I want to add the values of two cells in a column. Both cells contain values I entered manually. The column is formatted for currency. Example:

A1 = $100.00
A2 = $50.00

If I highlight the two cells (A1:A2) and click on ∑ (sum) in the function bar, it will automatically generate the formula [=SUM(A1:A2)] in cell A3, and in this case, display a value of $150.

SCENARIO 2
I want to add the values of two cells in a column. One cell (A3) is has value derived from a formula; the other cell (A4) has a value I entered manually. The column is formatted as in scenario 1.

A3 = $150.00
A4 = $50.00

If I highlight the two cells (A3:A4) and click on ∑ (sum) in the function bar, it generates the formula [=SUM(A4:A4), omitting the cell (A3) from the formula and displaying a value of $50.00.

Obviously I want to be able to click on ∑ (sum) in the function bar and have it add the value generated by the formula to the next value. The formula should be [=SUM(A3:A4) which in this case would yield a value of $200.00. Of course I can manually enter the formula and it will work.

There must be a setting that has been turned off or needs to be turned on to allow this action.

If you do not understand my query, I can upload an example Calc file. Thanks for your help.

[fpy]

@ChristophC

able to click on ∑

actually, don’t highlight anything;
go to A5
click on ∑
→

then extend the selection to A3, Enter

See also : Select Function

[ChristophC]

That works. Thank you.

[fpy]

@ChristophC

works

for future readers, please #Mark_an_answer_as_Solution → https://wiki.documentfoundation.org/Ask/Guide_-_How_to_use_the_Ask_site#Mark_an_answer_as_Solution

[ChristophC]

Just to clarify, using fpy’s suggestion above, I found this solution to work:

1. Go to A5.
2. Click on ∑ and select Sum.
   A formula is generated that does not include A3 so you must extend the selection.
3. Click on A3 and drag to A4 to extend selection.
   
4. Press Enter.

Again, credit to fpy. But it still took me a few attempts to figure out how to extend the selection. Simply pressing the up arrow or shift and up arrow did not work. Still seems like you should be able to set AutoSum to include formula cells, but this solution works.

[ChristophC]

Of course the easiest solution (if you are repeating this pattern many times) is to simply copy and paste.

1. Click on A3.
2. Press command + C to copy.
3. Click on A5.
4. Press command + V to paste.
   Calc automatically adjusts the formula for the two cells above.

Note: People not used to using shortcut keys (like my mom) may find this solution more difficult and find the former solution easier to navigate.

[fpy]

@ChristophC

how

Shift-click on A3 also extends the selection

[fpy]

@ChristophC

shortcut

and if you want to assign a shortcut, this macro sum() the selected range in one go :

sub sumSelectionAbove
  sel = ThisComponent.CurrentSelection
 
  sheet = ThisComponent.CurrentController.ActiveSheet
  sum = sheet.getCellByPosition(sel.RangeAddress.startColumn, sel.RangeAddress.endRow+1)
  sum.formula = "=SUM(" & sel.absolutename & ")"
end sub

[cwolan]

@ChristophC

But it still took me a few attempts to figure out how to extend the selection. Simply pressing the up arrow or shift and up arrow did not work.

Indeed.
A small keyboard play just for fun (or would that be overkill?)…

Scenario 1
Go to A5, press Alt+= (keyboard shortcut for AutoSum). Notice that the range “A4:A4” is higlighted on the input line.

Press Left Arrow until you reach the first digit “4”, change it to “3” and confirm.
⠀
⠀
Scenario 2
Select A3:A4, press Alt+=
Press F2 to edit,

• change the first digit “4” to “3”
  OR
• select “A4:A4”, press F2, press Up Arrow and extend the selection range to A3 with Shift+Up Arrow

and confirm.
⠀

@ChristophC

Still seems like you should be able to set AutoSum to include formula cells, but this solution works.

See:
tdf#164966 - Σ button generates a formula with a wrong range in some cases
tdf#149041 - Calc SUM button confusing with selected cells