Q: Count how many wins across tabs

Hi Beh,

Accumulating summary data across a number of tables is always more difficult that extracting similar data from a single table, but is doable, especially what the several contributing tables are sequentially named.

I'd suggest a two step approach here:

1. Collect the Winner! data from all of the auxiliary tables.
2. Summarize the collected data.

The data to be collected in on row 5 of each of the auxiliary tables. Names of the participants are in row 1 of each auxiliary table, and are in the same order on each.

The auxiliary tables are organized with the names across the first row; your proposed "Total" table is organized with the names in the first column, which complicates matters. I would suggest using the Auxiliary table pattern for calculating the results, then transposing them to the pattern of the Total table.

Here's a sample set of Aux tables for 10 weeks (Aux1 is the table in my earlier example, Aux2 through 10 are copies of that table with random "W"inners inserted in the bottom row.

Summary:

All Aux tables contain the "W"inner results in row 5. (Aux1 also includes "n" in non-winner cells—a leftover from my early tests.)

Names of these tables are identical except for the number indicating the week to which the Aux is linked. This allows the Row number, or a number in column A of the Summary table to be used in composing an INDIRECT reference to row 5 of each table. The names of these tables are distinct within the document, so the Sheetname for each may be omitted from the range reference constructed with INDIRECT

Row 1: The names in Row 1 are direct transfers from the Row 1 cells of Aux1. Because they address a single table, INDIRECT is not necessary here:

Summary::B1: INDEX(Aux1::1:1,COLUMN())

Fill right to column F.

Rows 2 through 11: Each row collects the "W" and null string values (or "n"s in row 2) from one of the Aux tables:

Summary::B2: INDEX(INDIRECT("Aux"&$A2&"::A:F"),5,COLUMN())

Fill Right to column F

Fill down to row 11

Row 12: This is a Footer row. The formula in each column counts the number of "W" occurrences in its column.

Summary::B12: COUNTIF(B,"W")

Fill Right to column F.

Total Weekly Wins:

This table uses INDEX to copy the names in Row 1 of Summary into column 1 of TWW, and to copy the totals from Row 12 of Summary into column 2 of TWW.

Total Weekly Wins::A2: INDEX(summary::$1:$1,ROW())

Total Weekly Wins::B2: INDEX(summary::$12:$12,ROW())

Fill each down its column as many rows as there are names across Row 1 of Summary.

Regards,

Barry

Both issues are due to a mismatch in the number of columns in Auxn and Summary.

Summary::B1: Index(Aux1::1:1,COLUMN())

This tells Numbers to look into row 1 of Aux1 (the range) and return the value from the nth column of the range.

COLUMN() returns the number of the cell in which the formula sits. B is column 2 of the its table, so COLUMN() returns 2, and the IINDEX formula returns the value from the second cell in row 1 of Aux1.

When you delete Column A of Summary, B1 becomes A1. The formula now sits in column A, COLUMN() returns 1, and  INDEX returns the value from the first cell in row 1 of Aux1.

Summary::B2: INDEX(INDIRECT("Aux"&$A2&"::A:F"),5,COLUMN())

Without the header column, your Aux tables have only five columns: A, B, C, D, and E. The formula references columns A:F of an Aux table. The 'bad reference' is to column F, which does not exist on the Aux table(s). Change 'F' to 'E'

Deleting column A of Summary puts COLUMN() in step with the data positions on Aux, but also removes the existing column A, which contains the Week numbers used to build the indirect address pointing the the individual Aux tables.

You can replace '&$A2&' with '&ROW()-1&' and get the same result, but without the visual week numbers on the Summary table,

OR

You can leave the rows and columns of the Summary table as is, and replace 'COLUMN()' with 'COLUMN()-1' in the formula to collect the data in column A of the Aux tables in Column B of Summary.

Regards,

Barry