Level 2 · Two exports, two sort orders

Level 1 worked because every block had the same rows in the same order. Real exports are rarely that tidy. One system sorts by id, another by name, a product with no sales is left out, a new one appears. Adding row 3 to row 3 then mixes different products. The fix is to match rows by their label, here the product id, before adding. Excel calls this consolidating by label.

Why position fails here

The marketplace’s September report is sorted by product id, low to high. October’s came sorted high to low:

Row	September (A:B)	Row	October (A:B)
3	141: 1 unit	17	160: 4 units
4	142: 2 units	18	156: 4 units
13	160: 1 unit	26	142: 3 units

On top of that, product 143 sold only in October and products 141 and 158 only in September. So September has 11 rows and October 10. No fixed offset can line them up.

SUMIF, once per block

SUMIF(range, criteria, sum_range) adds the cells of sum_range on every row where range equals the criteria. Point it at one block and it finds the product wherever it sits:

=SUMIF($A$3:$A$13,D3,$B$3:$B$13)        units in September
=SUMIF($A$17:$A$26,D3,$B$17:$B$26)      units in October

Add the two in one cell:

E3   =SUMIF($A$3:$A$13,D3,$B$3:$B$13)+SUMIF($A$17:$A$26,D3,$B$17:$B$26)

For product 141 that is 1 + 0 = 1: it is not in the October block, and SUMIF returns 0 when nothing matches instead of an error. For product 154, the best seller, it is 13 + 16 = 29.

The block ranges are locked with $ because every row reads the same blocks. D3 stays relative so each row looks up its own product.

Where the label list comes from

The list in D3:D14 holds every product that appears in either block, 12 in all. Excel’s Consolidate tool builds that list for you. With formulas you build it yourself: copy both id columns under each other, then Data → Remove Duplicates and sort. If you skip a product, its units silently vanish from the total, so check the grand total: =SUM(E3:E14) must equal =SUM(B3:B13)+SUM(B17:B26). Here both are 96.

The Consolidate dialog, by label

In Excel, with each month on its own sheet: Data → Consolidate, Function: Sum, add each source range including its id column and header row, tick Top row and Left column, and click OK. Excel matches rows by the left column and columns by the header text, and lists every label it found. Two things to know: the labels must match exactly (141 the number and "141" as text are different labels), and the result is static unless you tick Create links to source data. Google Sheets has no Consolidate tool; the SUMIF version works there unchanged.

Common mistakes

• Adding by position. =B3+B17 gives product 141 five units: its own September unit plus product 160’s four in October.
• Using a lookup instead of SUMIF. XLOOKUP or VLOOKUP returns #N/A for a product missing from one block, and only the first match if an id appears twice. SUMIF returns 0 and adds every match.
• Unlocked block ranges. A3:A13 filled down becomes A4:A14, then A5:A15, and the last rows stop seeing the top of the block.