Total a cell across multiple sheets

Here's a means that gets a simpler formula by splitting the tasks into gathering, sorting and totaling between two formulas and an auxiliary table.

For the example, the document has seven "Day" sheets (left with their default names), each containing a single table, each with four cells from which data is to be collected and summed (B3, C3, D3 and F4. "Today" is the 5th off the month, so only the first five tables contain data, and for simplicity, each of the target cells contains the same data on each day.

The final Sheet, renamed SUMS, has a table on which a formula collects the data from the target cells on the entry tables, and lists each cell's values in a column. The formula, shown under the table below, uses indirect to interpret the cell reference address constructed from text in the formula, the number in column A and the cell location in row 1.

The data in each column is summed by SUM(B) in the Footer row of each column.

The formula is entered as shown in B2, then filled down to the last non-footer row (B8 in the example) and filled right to the last column.

B9: SUM(B)

Filled right to the last column.

The totals can be transferred to the appropriate ell in your existing Summary table using a simple cell reference.

Regards,

Barry

Hi tan'

You may want to reconsider the structure of your Spreadsheet document.

Much easier to use a single Data table (rather than 186 separate tables) on which to enter the data on and 31 Daily Summary sheets, each with six tables to extract and summarize the data for each day.

Using the setup you currently have, I'd start by renaming the Tables, using only the daynumber and table number, separated by a dash.

Sheet 1 would then contain these tables: 1-1, 1-2, 1-3, 1-4, 1-5, 1-6

Sheet 2 would contain these tables: 2-1, 2-2, 2-3, 2-4, 2-5, 2-6

Et cetera.

The formula for each total would look like this one (for the total of values in cell B3 of tables x-1)

=SUM(1-1::B3,2-1::B3,-1::B3,4-1::B3,5-1::B3,6-1::B3,7-1::B3,8-1::B3,9-1::B3,10-1::B3,11-1::B3,12-1::B3,13-1::B3,14-1::B3,15-1::B3,16-1::B3,17-1::B3,18-1::B3,19-1::B3,20-1::B3,21-1::B3,22-1::B3,23-1::B3,24-1::B3,25-1::B3,26-1::B3,27-1::B3,28-1::B3,29-1::B3,30-1::B3,31-1::B3)

Repeat for each of the totals required.

With your Sheets and Tables as currently named, each of these cell references would expand to include the full name of the Sheet and the name of the table on that sheet:

=SUM(Sheet 1::Table 1::B3, … ,Sheet 31::Table 1::B3) (with … replaced with all 29 other addresses in the list, separated by commas.

For an example of the type of formula possible when ALL transactions are recorded on a single Data table, take a look at the Summary by Categories table i the Personal Budget template in the Template Chooser.

Regards,

Barry