10/25/10
3:20 pm

# Forum Question: Numbers – Running total in single cell

I’m new to macs and new to spreadsheets, and have looked at tutorials all day and can’t figure out this one thing. Is there a way to do a running total in one cell? I’m working on a family budget, and looking back at our expenditures over the past 12 months, gathering data from credit card and bank statements. Say I have an item called “Groceries” and there are several posts on my cc statement for the month of October ’09. Is there a way I can keep a running total within the cell? I’ve completed October ’09 with the help of a calculator, and this feels like cheating. I’m determined to do November ’09 – October ’10 using Numbers only.
—–

### Comments: 13 Responses to “Numbers – Running total in single cell”

10/25/10 @ 3:25 pm

Sure. What you want to do is a sum. For instance, the tenth row in column A might be =SUM(A1:A9) which gives you the total of rows 1 to 9 in column A. Insert a new row in there and the formula should change to =sum(A1:A10) automatically.
See episode 255: http://macmost.com/using-spreadsheet-formulas-in-iwork-numbers.html for an example.
If you set the table up right, then the header row should be the title, like “Credit Card Charges” and then add a footer row that is simply =SUM(A) — that will keep a total of all of the rows in that column, except the header and footer rows. Very cool way to do it. Just add more rows to the table to add more numbers to the sum.

Jerry
11/2/10 @ 6:18 am

If you want to sum only the amounts associated with the category “Groceries”, you can use a variation of the SUM function called SUMIF. If, for example, your main table has the Date in column A, the Category in Column B and the Amount in Column C, this expression will add up all your “Groceries” items:

=SUMIF(B, “Groceries”, C).

This type of sub-total calculation is sometimes best done in a separate table so you don’t disrupt the layout of the main data table. If you want to use a separate table, you then must change the expression to include the name of the data table. If, for instance, your data table is called “Charges”, your expression for the Groceries sub-total in a separate summary table would be:

=SUMIF(Charges :: B, “Groceries”, Charges :: C).

Jerry

Julie
11/2/10 @ 10:25 am

If I’m not mistaking the question, it is the same one that I have. I need a running total in a single cell…exclusive from all the other cells. For example, If I have a 5 in a cell and need to add 2 more to it, I want to enter the 2 in the cell and have it do the addition and change to a 7. Does this make sense and is it possible in Numbers?

Thanks!

11/2/10 @ 2:21 pm

If you had 5 in the cell A1, and then put 2 in the cell A2, and then had the formula =A1+A2 in cell A3, then A3 would show the number 7. You could also do =SUM(A1:A2) as the formula. Is that what you mean?

Jerry
11/3/10 @ 7:30 am

Hi Julie,

I read your question as “can I type over a previous entry and have a sum of the previous content and the new entry appear in that same cell”. When you type over a previous entry there is no way to recall what used to be there and add it to the new entry. You will simply have the new entry.

Jerry

Byron
11/8/10 @ 11:37 pm

Hi Julie,
You cannot overtype directly into the cell. You CAN do essentially what you want by typing directly into the formula/function bar.
When you enter data in cell you will see it also in the function/formula bar at the top of the spreadsheet
If in hte function/formula bar you enter =sum( ) then enter between the brackets 2,2 or 2+2 thus
=sum(2+2) you will get 4 in the highlighted cell. If you then add 22 to that, thus =sum(2,2,22) or =sum(2+2+22) you will get 26 in the selected cell.
This is about as close as you will get to a running total as you describe.

John R.
3/1/12 @ 8:25 pm

I am trying to maintain my check register on Numbers – having brought over my AppleWorks version. However, AppleWorks would do a running total (last total minus check plus deposit simply by highlighting the last total and the new total box and executing Command-D (fill down). That would paste the formula down a row with a new total. Does numbers have an equivalent way to paste the formula down to the next row without having to write in the formula every time. The user guide is NOT clear on this. Many thanks.

3/1/12 @ 10:44 pm

Numbers will certainly do this. The way would be to set up a footer row with the formula in a table. Then inserting a row above the footer would mean the footer formula would include all of the rows, including the new one. See episode 475: http://macmost.com/adding-up-amounts-in-iwork-numbers.html

John R.
3/3/12 @ 12:42 pm

Thanks Gary. But that method seems to be set up for sums, averages, etc. of number straight down the column. I’m using 7 columns. The formula is (G1)-(D2)+(F2). The running total should then appear in G2. How would I set that up? Many thanks.
John R

John R.
3/3/12 @ 6:59 pm

AHA! I found the solution. Copy and paste the total down the rest of the column. The formula goes with it.

7/3/12 @ 9:18 pm

If you have your Numbers sheet set up like a checking account, how do you get the total to reflect the deposit listed and then how do you get the checks written to show subtracting in the total column?

7/4/12 @ 12:21 am

Just have a row with the running total next to them. Have each one add the deposit column and subtract the withdrawal column. I think there is even a template for this too that you could examine.

Chris Wiseman
9/25/12 @ 3:02 pm

I am struggling with a similar problem not exactly what I have read here.
Example cell C3 represents allowed money value for a given item in my budget spreadsheet . Cell E3 contains the actual cost for the week.
Cell G3 contains the sum of C3and E3. This is repeated in the rows below for all the different items in the budget. At the end of the week I want to save the sheet asand continue with the same contents in each of the G column entries as a running total and so on with each weekly sheet through the year.
Problem is I need to alter the entries in C and E columns for the changing amounts for that week but as you know, if I replace the entry in cell C3 or cell E3 the figures will not maintain a running total.
Does anyone know how to code up for that? The running total needs to run through each weekly sheet for the entire year.
This provision is commonly in premade commercial accounting programmes so I know it can be done.
The commercial packages however, cant be designed to suir ones individual requirements. Anyone know the coding or formula please. Thanks