Statistical FunctionsIntermediate

Total each order's region revenue with SUMIFS and check it against a bonus threshold

SUMIFS matches rows on a criterion without needing the data sorted or grouped first, so every order from the same region can show the same regional total just by pointing at that order's own region cell.

Task:

You track quarterly orders for a sales organization split across regions, and the rows stay in whatever order they were entered rather than grouped by region. Finance pools bonus money by region, and a region only shares in the pool if its combined revenue across all its orders this quarter reaches $9,000. In column D, use SUMIFS to total the revenue for each order's own region, so every order from the same region shows that region's full total. In column E, mark each order "Yes" if its region's total meets the $9,000 threshold, otherwise "No". Fill in rows 2 through 6.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 5 columns10 cells you fill in
ABCDE
1Order IDRegionAmountRegion TotalBonus Pool
2ORD-101East4200
3ORD-102West3100
4ORD-103East5300
5ORD-104Midwest2800
6ORD-105West6200
What this exercise teachesMay contain the answer

SUMIFS($C$2:$C$6,$B$2:$B$6,B2) doesn't care which row it's typed into — it scans the whole region column for every cell that matches B2, and adds up the amount sitting next to each match. Because B2 holds "East", the formula in row 2 finds both East orders (4200 and 5300) and returns 9500, the same total that row 4's copy of the formula also lands on, since B4 is "East" too. The $ signs on the two ranges are what make that work after copying: without them, row 4's formula would scan C4:C8 and B4:B8 instead, a window that has slid past the top of the data and no longer holds the same five orders row 2 was comparing against. Only the plain cell reference — B2 versus B4 — is meant to change as the formula moves down, which is exactly why it's the one reference left unlocked. Once that region total is sitting in column D, IF(D2>=9000,"Yes","No") stops caring how the number got there; it only compares it against the threshold, which is why Midwest's single $2,800 order fails where every East and West order passes, even though no single order in column C is anywhere near $9,000 on its own.