adding a totals sheet

I have a cell that totals up numbers on each of 12 sheets (named by month) I want to have a totals sheet say "totals sheet 13" that will give me all the totals I request from previous sheets (automatically transfered". Can I do this?

iMac 27″, macOS 13.4

Posted on Jul 16, 2023 8:17 AM

Reply
Question marked as Best reply

Posted on Jul 16, 2023 11:01 AM

The most direct way is to add each of the 12 cells.


=January::Table 1::B20+February::Table 1::B20+...etc


Another way involves a summary table:



Text in column A and row 1 is typed in manually.

Formula in B2 =INDIRECT($A2&"::"&B$1)

Fill down for the rest of the months

Fill right if you have other cells to collect from these sheets


Formula in B15 (footer row) = SUM(B)

Your Totals table will then refer to that sum.

You don't really need this footer formula, you can SUM them in your Totals table or do averages or whatever other manipulations you need to do on the 12 values.

5 replies
Question marked as Best reply

Jul 16, 2023 11:01 AM in response to Bob-Ru

The most direct way is to add each of the 12 cells.


=January::Table 1::B20+February::Table 1::B20+...etc


Another way involves a summary table:



Text in column A and row 1 is typed in manually.

Formula in B2 =INDIRECT($A2&"::"&B$1)

Fill down for the rest of the months

Fill right if you have other cells to collect from these sheets


Formula in B15 (footer row) = SUM(B)

Your Totals table will then refer to that sum.

You don't really need this footer formula, you can SUM them in your Totals table or do averages or whatever other manipulations you need to do on the 12 values.

Jul 16, 2023 4:24 PM in response to Bob-Ru

One thing about the table with the INDIRECT functions is that they will not adjust like regular cell references if the cell moves (such as if a row is added above). INDIRECT("January::Table 1::B20") will be that exact cell and stay that exact cell.


There is a different way to do the table method that uses the "header names as labels" feature of Numbers. Instead of "Table 1::B20" in the header row of the summary table you use the label for that cell. If in the month tables the column B header has "1" and the Row 20 header has "Total" then it would be "Table 1::Total 1". This method will point to whichever cell in Table 1 has the label "Total 1". You cannot have two columns with the name "1" or two rows with the name "total" or this method breaks.


For only 12 additions it might be simplest to just add them the usual way vs using the table method.

Jul 16, 2023 5:47 PM in response to Bob-Ru

I found another way that works for my use. I entered the "equal" sign to start the formula where I wanted it in sheet 13 (totals) and then went to the cell starting in the first month that I wanted the formula to pick up and clicked on that cell and it added it all I did then was click the green check sign and it was done. I checked it by changing the total number in the month and going to the totals sheet and yes it is automatically changing as I had wished.

adding a totals sheet

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.