Level 3 · One master table, totals per product and channel

Consolidating by position or label gives you totals, but the detail is gone: you can’t ask a new question of the result. The other approach keeps every row. You stack all sources into one master table, add a column saying where each row came from, and summarise that table however you need. It’s more work up front and far more flexible afterwards.

Stacking the sources

Rachel’s master table is three exports pasted under each other: web lines in rows 2–11, app lines in rows 12–24, marketplace lines in rows 25–33. Every export had the same columns, and before pasting she filled the new source column with the export’s name. That column is the whole trick: once the rows are mixed, it is the only way to tell them apart again.

Rules for a master table that stays useful:

Rule	Why
Same columns, same order, same names in every source	Pasted blocks must line up under one header
One header row, at the top only	Header rows in the middle become data rows
A source column filled on every row	Without it, per-source totals are impossible
No blank rows or subtotal rows	Totals would count them, and filters and PivotTables stop at blanks

Stacking is copy and paste here. In Excel with real exports you would use Power Query’s Append instead, which repeats the stacking with one click when next week’s files arrive (chapter 11).

One formula for the grid

Each cell of the grid needs revenue where two things are true: the product is the one in column G and the source is the one in row 2. That is SUMIFS:

H3   =SUMIFS($E$2:$E$33,$C$2:$C$33,$G3,$A$2:$A$33,H$2)

The ranges are locked completely. The two criteria are each locked on one side only, the mixed-reference pattern from chapter 3:

Reference	Filled right	Filled down
$G3	stays in column G	moves to G4, G5… (next product)
H$2	moves to I2, J2 (next source)	stays in row 2

Product 141 on the web: 10.49 + 13.99 + 55.96 = 80.44. Product 149 in the app shows 0, because no app order bought it this week; SUMIFS returns 0 instead of an error.

Reading the result

The marketplace is where this week’s big cookware money came from: 545.00 for product 149 and 291.96 for product 153. Check the grid the usual way: =SUM(H3:J7) must equal =SUM(E2:E33), 2,101.55 on both sides. A gap means a product missing from column G or a source name spelled differently in the data.

Common mistakes

• Text that almost matches. SUMIFS ignores case but not spaces: a source typed as "web " in the data never matches the header web, and its revenue disappears without an error.
• Ranges of different sizes. $E$2:$E$33 with $C$2:$C$30 returns #VALUE!. All ranges in one SUMIFS must cover the same rows.
• Pasting a second header row. If each export is pasted with its header, rows like source, order_date, … end up in the middle of the data. Paste the rows only.