Cumulative sum function

[djwfam]

I just got booted off my excel starter program after upgrading to Windows 10, so I'm trying to set up my spreadsheets in OpenOffice. I want to be able to add to my subtotals but when I hit sum, instead of =SUM(B3:B4) it inputs =SUM(B4) and so on. Also, if I want to add a row and input a new number, when I hit sum, it doesn't acknowledge the new row. How do I correct this?

[acknak]

Greetings and welcome to the community forum!

Sorry, I'm not sure I understand what you're doing/looking for here. Can you explain a simple scenario, step-by-step, telling us what you entered (and where), what you mean by "hit sum" (press a key? click a button? ...), and so on.

It often helps if we can see the sheet you're working on; you can attach it here (if it's not too big) or you can make a sample sheet so we can see the problem clearly. Use the "Upload Attachment" link (below the message entry area after you click "POST REPLY").

[djwfam]

K I tried to post a screenshot but it's not uploading quick enough. If I add 50+50 down a column and hit sum function icon I get 100. When I add 10 to that subtotal instead of 110 I just get 10.

[djwfam]

okay, so i attached a document, as you can see if i add 100 to 450 and click on the "sum" function key, i get 550. then when i try to add 50 to my subtotal of 550, and use the same "sum" function key it does not add from my subtotal.

[djwfam]

nevermind, i uploaded the wrong document, but this one illustrates the same point. you can see that lines 1-7 is what i am attempting to do. i was able to achieve those sums by manually changing the formula in the box beside the sum function key. you can see that at the bottom when i try to add 500 to $10,730.84, it just pulls up 500. it does not add to my subtotal. i want to be able to click the 'sum' function key and cumulatively add to my subtotals automatically.

[RusselB]

If your numbers are in a single column, insert a row at the top of the column and enter a SUM function that covers the number of rows you might use.
For example, if you know you're going to have no more than 1000 numbers, and your numbers are all in column A, then insert a row at the top of column A (making a new row 1), then enter

Code: Select all

=sum(A2:A1001)

Then the total, which is in A1, will update as you add more numbers into column A, up to (and including) row 1001

You won't be able to do this using the SUM key, but by entering the formula manually, it's quite simple.

[djwfam]

I don't really get it. I don't know if I did it right and it didn't fix the problem. I absolutely need to have the sum key enabled to automatically add to my subtotals, I can't have something that needs to be inputted with a manual formula every time I add to the subtotal because of the sheer number of times I will need to do so. In Excel it was so easy, I just kept adding and subtracting from my subtotals with a simple click of a sum key. If I'm not going to be able to get that to work, let me know so I can look for another program.

[acknak]

Sorry, I don't think there's any way to get the SUM function button to work like you want.

Can you work with a different arrangement?

Try the attached file. Just enter the next amount in C6; the new total will appear in B6.

[RusselB]

It sounds to me like you were using Excel's GRANDTOTAL function, not the SUM function.
The GRANDTOTAL function does a SUM of SUBTOTALs
It is possible to set the SUM button in Excel to generate the GRANDTOTAL function, rather than the SUM function

[FJCC]

This is how I see Calc working. I have numbers in cells A2:A6. I select A7 and then click the sum button and hit Enter. A7 now contains the formula

Code: Select all

=SUM(A2:A6)

I click on A7 again and select the menu Insert -> Rows. This inserts a new row 7 so the SUM() formula is now in A8 and reads

Code: Select all

=SUM(A2:A7)

If I input a number to A7, it is included in the sum.
The automatic expansion of the formula range happens only if you have gone to the menu Tools -> Options, expanded the Calc list on the left, chosen General and selected Expand References When Column/Rows Are Inserted. If you don't have that option turned on, then after inserting the new row and entering data you have to click the SUM key again when cell A8 is selected.

The above is an explanation of how I would do what I think your are trying to do. If I'm wrong, please explain more clearly what you are trying to do. The phrase "adding and subtracting from my subtotals" is ambiguous. Are you adding and removing data from a column? Are you erasing the content of cells or actually deleting the cells? Please tell us what cells contain data and what you do to change the data set.

[djwfam]

okay, so i updated the preferences and yes, now i can insert a row into my column and it will update my subtotal. here's what i am still unable to do. the first sum is fine, let's say it's 1+1=2. A1 I would put 1, A2 I put 1, I left click on A1, drag my mouse to select A1,A2,A3, hit sum and the answer shows up in A3 as 2. So far so good. Then I want to add to my subtotal. So I would put 1 in A4, left-click on A3, drag mouse to highlight A3,A4,A5, hit sum key and the answer SHOULD BE 3, but it shows up as 1. it's not carrying the subtotal from A3, which is 2, down into the new equation.

Here's what I'm trying to replicate from excel. It's for budgeting purposes. So, I put my starting bank account balance in B1 (column A contains description of expenses + income, column C contains date) so let's say i start with $1000 and then have an expense of $500. B1 contains $1000, B2 is -$500, B3 is my subtotal at $500. Then i get paid $500 so I add that to B4, left click on B3 to highlight B3,B4,B5 and my subtotal should be $1000, then say 3 expenses come out, so i have $1000 in B5, -100, -100, -200 in B6, B7,B8, left click on B5, drag to higlight B5,B6,B7,B8,B9, new subtotal in B9 is $600. and so on and so on and so on. I usually input the next 6-12 future months of income and expenses with all it's subtotalling down the column, so that i can see where we're at down the road, and then as things *actually* come out I just change the font colour to grey to show it's done, add rows in where extra expenses came up etc, so being able to add rows to the formula is super--but we've got that figured out.

It's just a matter of figuring out how to be able to get the sum function key to recognize that i want to use my subtotal as a number in the next equation. Does that make it at all clearer?

[RusselB]

I strongly recommend moving your subtotalling into a different column from your actual financial entries.
In some Bookkeeping and Accounting, they actually use three columns. One for debit, one for credit, and one for balance.
If you want to only use one column for your financial entries, fine, but please consider my suggestion.

[Alex1]

You can try this: select the cell below the last entered number, click the sum button, drag the lower right corner of the blue rectangle upward until the rectangle contains the numbers to be added, then press Enter.