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.
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.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order ID | Region | Amount | Region Total | Bonus Pool |
| 2 | ORD-101 | East | 4200 | ||
| 3 | ORD-102 | West | 3100 | ||
| 4 | ORD-103 | East | 5300 | ||
| 5 | ORD-104 | Midwest | 2800 | ||
| 6 | ORD-105 | West | 6200 |
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.