[Solved] Sum negative values with Σ button

[bobbee25]

I have a column where I sum 3 or 4 numbers.
Now I want to add a negative number (subtract)
but it can't seem to apply the neg number to the summed number,

Example 10
25
sums 35 (=sum(b3:b4)
now add -15 (to the 35)
result -15 (=sum(b5))

Should be 20 ????????????????????????

Edit: Changed subject, was sum calc value with neg number  Make your post understandable by others  -- MrProgrammer, forum moderator

[RoryOF]

Why not simply =Sum(b4:b6)?

Sum() sums an entire range, no need to specify individual items. Alternately, you could use =B4+B5+B6 and the sign of B6 will be taken into account.

[keme]

I have a column where I sum 3 or 4 numbers.
Now I want to add a negative number (subtract)
but it can't seem to apply the neg number to the summed number,

Example 10
25
sums 35 (=sum(b3:b4)

... Makes sense so far ...

now add -15 (to the 35)

Exactly how do you "add -15"? Did you type "-15" into the cell where your sum is? Do you have the value in a different cell?

result -15 (=sum(b5))

That sum function only fetches value from one single cell, so it should just repeat the B5 value. What do you have in cell B5? Your original sum formula, the added -15 value, or something else?

At best, save your spreadsheet to a file and attach that file to a posting here. See the Upload attachment tab below the edit pane when you post an answer.

[bobbee25]

an example (works ok with excel)

[Zizi64]

Example 10
25
sums 35 (=sum(b3:b4)
now add -15 (to the 35)
result -15 (=sum(b5))

Should be 20 ????????????????????????

Please upload your .ods type sample file here.

[RoryOF]

This is an X-Y problem; do you need the intermediate values, or just the final; if only the final amount, In C12, enter =Sum(C7:C11)

[MrProgrammer]

an example

Please do not attach an image. Images do not show how the spreadsheet is organized. The best way to get assistance is to attach a document. It's also confusing for us when your second example (using column C) is different than earlier one with column B. In general, when you provide multiple examples it could be confusing for you to determine which example a forum response is for. Avoid doing that.

works ok with excel

That is not relevant in the Calc forum. Use Excel if it better meets your needs. Calc is not a clone of Excel.

Now I want to add a negative number (subtract) but it can't seem to apply the neg number to the summed number

The difficulty here is not the negative number. I believe you will find that the same thing happens when C10 has a positive number. Try it, bobbee25. I did.

My guess is that you have attempted to use the Σ icon (Sum button) on the formula toolbar. You said "sum" in your post but did not say "icon" or "button" and did not explain in detail how you performed your "sum". Using Calc's Σ icon can be confusing. Calc is trying to help providing an easy way to get subtotals and a grand total. Here's an example for you to try. It will be easier to understand if you actually perform these actions in a spreadsheet than if you just read the example.
• Put 3 and 4 in E2 and E3. Click E4 and use Σ. Calc creates formula =SUM(E2:E3) with value 7 (3+4).
• Put 5 and 6 in E5 and E6. Click E7 and use Σ. Calc creates formula =SUM(E5:E6) with value 11 (5+6).
• The sum is a subtotal which includes only the cells containing numbers above it and does not include the earlier subtotal (SUM formula) in E4.
• Put 7 and 8 in E8 and E9. Click E10 and use Σ. Calc creates formula =SUM(E8:E9) with value 15 (7+8).
• The sum is a subtotal which includes only the cells containing numbers above it and does not include the earlier subtotal (SUM formula) in E7.
• Finally click E11 and use Σ. Calc creates formula =SUM(E10;E7;E4) with value 33 (7+11+15).
• This is the grand total of the subtotals.

In your case you used the Σ icon for C11 and Calc added the numbers above it with formula =SUM(C10) to get value -13.

However you don't want to produce subtotals and a grand total. You want a running total. You cannot use the Σ icon to do that. You must type the formula yourself. In this case that would be =C9+C10 or =SUM(C9;C10) (using a semicolon) or =SUM(C9:C10) (using a colon). Learn more by reading the Ten Concepts tutorial below.

The standard way to get a running total is to use a separate column. That layout makes the formulas simpler.
Automatic SUM balance per row Count

Thank you for the interesting question.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know