Level 2 · From order lines to order totals with SUMIF

A sales export has one row per order line: a lamp and a throw in the same basket are two rows. Most questions, though, are about orders: what did each customer pay, how many orders did we get, what is the average order worth? This level builds the bridge between the two, and the rest of the chapter stands on it.

The sheet holds every line of the 23 orders placed on Monday, November 17, 2025: 54 lines in A2:E55. Column H lists each order id once. Amanda’s full month is 1,600 lines; once the formulas work on this sample, they work on the month.

Step 1: what each line earned

The revenue definition is the same one the SQL and pandas holiday case uses: quantity × price × (1 − discount ÷ 100). In F2:

=C2*D2*(1-E2/100)

Fill it down to F55. Most lines have no discount, so F2 is simply 1 × 159 = 159.00. Row 6 is order 11829, which used a 10% code: 1 × 139 × 0.9 = 125.10.

Step 2: SUMIF by order id

SUMIF adds the values in one range where another range matches a criterion:

argument	here	meaning
range	$A$2:$A$55	where to look for the order id
criteria	H2	the id to match
sum_range	$F$2:$F$55	what to add up, row by row

I2   =SUMIF($A$2:$A$55,H2,$F$2:$F$55)

For order 11829 (H5), SUMIF finds three lines in rows 6 to 8 and adds 125.10 + 27.89 + 429.30 = 582.29. The two ranges must be the same size and start on the same row, because the function pairs them up position by position.

Lock both ranges with $ and leave H2 relative, then fill down to I24. Every row searches all 54 lines for its own id.

Check that both sides agree

A roll-up must not create or lose money. Put =SUM(F2:F55) and =SUM(I2:I24) in two spare cells: both show 10,937.42. If the order side is lower, an id in H is missing or mistyped; if it is higher, an id is listed twice. This one check catches most roll-up mistakes before anyone else sees them.

The same habit matters when you move to the full month. In the SQL case, November 2025 comes out at 651 orders and 292,364.40 of net revenue. A spreadsheet built from the same lines with the same definition has to land on exactly those numbers, and level 6 shows that it does.

Why lock ranges that seem to work unlocked

Try =SUMIF(A2:A55,H2,F2:F55) without dollar signs. On this sheet it happens to give the right answers: filled down, I10 searches A10:A63, and because the lines are sorted by order id, order 11834’s lines (rows 16 and 17) are still inside that window. Sort the lines by category tomorrow and the window starts skipping lines that belong to the order. Lock the ranges so the formula does not depend on how the data happens to be sorted.

Common mistakes

• Summing the price column. $D$2:$D$55 adds unit prices and ignores quantity and discount: order 11829 shows 328.99 instead of 582.29.
• Swapping range and sum_range. =SUMIF($F$2:$F$55,H2,$A$2:$A$55) looks for the order id among the line totals, finds nothing, and every order shows 0.
• Ranges of different sizes. Keep the criteria range and the sum range the same height and starting row, or the rows are paired wrongly.
• Treating the discount as a fraction. (1-E2) turns a 10% code into a negative line: row 6 becomes −1,251.