Sum of totals of different on different tables

No intention to insult.

First part of this is mostly for my benefit in clarifying the details. Jump to the line in bold type to go straight to the suggested solution.

I took a closer look at your screen shots this morning, and think have the structure in hand now.

The tables "Income" and "Expenses" are 'Data' tables and contain no formulas.

The small table on the "Summary" sheet also contains no formulas, and is used to determine the month for which the two larger tables are to calculate the total amount of income in each category listed on the "Income" table and the total values of Expenses in each category listed in the "Expenses" table. Both these calculations are done with a SUMIFS formula summing values in column D of the "Income" table on the "Income" sheet or the corresponding table on the "Expenses" sheet that are in the range of dates determined by the data entered in the small table(s) on this sheet.

Because the contents of these two Summary tables is date dependent, and therefore volatile, the Months totals displayed on these summary tables is not the best source of data to calculate the amount you want on the Net Total table.

For that, use a formula that gets its data from the entered data on the Income and Expenses tables on the first two sheets, using this formula in Net Total::B2

SUM(Income::Income::F)-SUM(Expenses::Expenses::F)

Regards,

Barry

"The spreadsheet for last month becomes closed when I change the date to this moon."

Why? Is this done automatically? Changing it to a manual operation, triggered by the user, would remove the difficulty of 'get(ting) the details for last month.

when I am in this month, and I would like to get the details for last month, I have got to go change the date to the last month.

Again, why?

it does not make it easier to get the totals of the last month. because I have to change the month to the last month.

Then, "make it easier by rethinking your document to keep that table open until you have gotten the totals of the last month. Once you have connected with them, you can go ahead and hide or lock the previous month's table.

The formula, then, does not give me the required results as per your help above."

If it does not give the correct results, you have not referenced the correct pair of cells.

If your 'previous month" is on a Table named "April", and

• the cell containing the total you want to get from that table is in cell F10,
• The cell containing the running total on 'this table' (named April) is D10, and the cell where you want the sum of those amounts is F10 on 'this table',

Then the formula, in F10 of the table named May, following the directions in my first reply would be:

April::F10+D10

Or, if you prefer, you could use: SUM(April::F10;D10)

Looking at your screen shots shows there are two tables, Income and Expenses from which to get the totals.

From the screen shoots, I would guess that each month has a similar pair of tables, that these tables have the same names on each sheet, and that each of the Sheets is named with the name of the month that is recorded on that sheet.

Because your document will have a dozen tables named "Income" and a dozen tables named "Expenses", you will need to use a longer address for each of the cells referenced in the formulas above.

April::F10+D10 will require the sheet name (April) and the table name (Income). SUM(April::F10;D10) will require similar changes:

April::Income::F10+D10

"SUM(April::Income::F10;D10)

Cell locations are arbitrary addresses, written before seeing your screen shots, and kept because although they are not the cell containing the grand total up to the end of that month (F10) or the running total for that month (D10), You've not yet said or shown what cells do contain those values.

Regards,

Barry

Hi JM

Don't know about you, but I'm getting more confused as this discussion goes on.

Perhaps some definitions and your answers to some questions might make what you want done and what you are currently working with more clear.

Vocabulary:

In Numbers:

• "Spreadsheet" and "Document" are both short names for a "Spreadsheet Document" and include all parts of that Document.
• A "Sheet" is a large 'canvas' on which 'objects' such as Tables, Shapes, Images, and Text boxes may be placed. Every "Spreadsheet" has at least one "Sheet". Each Sheet has a distinct name, which is displayed in a Tab at the top of the Document Window. (Some users use "Tab" to refer to the Sheet ro which that Tab is connected.
• A "Table" is a rectangular array of rows and columns of cells. Every Table has at least one cell. Each Table has a name, which must be distinct from the names of other Tables on the same Sheet
• A "cell" is a 'box' which may contain entered data, placed there by the user via the keyboard, or calculated by a formula using data from one or more retrieved from another cell on the same table or from one or more cells on the same table or from one or more cells on other tables in the same Spreadsheet Document.

The full address of a cell consists of these parts:

sheetname::tablename::cellcolumncellrow

Main::Data::B4

would retrieve the value from the cell in the second column (B) and fourth row (4) of the table named "Data" on the sheet named "Main"

With that in mind:

You wrote (emphssis added):

I would like to take the Net Total of April and add it with the Net Total of May. so that the April Net Total will be carried forward to May and be added to the May Net Total.

As of today (May 4, 2022)

What is the full address of the cell containing the "Net Total of April"?

What is the full address of the cell containing the "Net Total of May"?

What is the full address of the cell in which you want to display the "May Net Total"?

My assumptions are that

"The Net Total of (month) means "the net total of transactions occurring in that month"

and "(month) Net Total" is the year to date total to 'today' or to 'end of this month', whichever date is earlier.

Regards,

Barry

Click on the cell where you want to see the result.

Press = to open the Formula Editor. Numbers will place the insertion point in the editor.

With the insertion point in the editor, click on the cell containing last month's total.

Type a Plus sign.

Click on the cell containing this month's total.

Click the green checkmark button to confirm the formula and close the editor.

The result will be shown in the cell containing the formula.

Regards,

Barry

PS: If this does not match the data layout in your document, please provide a more detailed description of the layout and organization of your data. A screenshot of each of the tables would also be useful.

It would help if you could provide specific details of what you are trying to do, rather than a general description that makes us guess.

To post a screenshot, shift-command-4, select an area with the "crosshairs", release, then start a new post here and use the "mountains and moon" Image Insertion icon below the compose window to attach the screenshot image file from the Desktop.

In general keep in mind that is usually MUCH more efficient to keep your data in one table, rather than split it into separate tables, one for each month. You can easily extract subsets by month from that one table using Filters, Categories, Pivot Tables and/or formulas using SUMIFS, etc.

SG

It's hard to follow because you've excluded row numbers and column letters in your screenshot. Have you tried following Barry's suggestion above?

(type =, click in cell for A, type + click in cell for B, enter?)

Still don't know why you are splitting into different tables...

SG

JediMaasai wrote:

I would like to take the Net Total of April and add it with the Net Total of May. so that the April Net Total will be carried forward to May and be added to the May Net Total.

Click in the cell where you want the net total for April to be carried forward, type = in the cell to call up the formula editor, then click the cell containing the net total for April and hit return.

SG

why not add a new date field(s).

you could then do FROM and TO dates.

incorporate that into your SUMIFS and you can use any date range you want to

This would allow you to do single months, weeks, and any date range you wanted

Jason

You are welcome, JediMaasai.

Thanks for the checkmark.

Regards,

Barry

PS: I see I neglected to check that the bold line had held that format before I clicked Post.

Apologies for that. Fortunately the post was shorter than it felt, so there wasn't a huge distance to scroll down.

B.