Build a Region by Product Sales Summary with SUMIFS
Your task: In G2, write one formula that totals revenue for a region and a product, then fill the whole table.
Full instructions, scenario and goal
Scenario
The sales director wants a one-page summary of last quarter's revenue by region and by product. The list of fourteen orders is in A1:D15, and the summary table in F1:I4 has the regions across the top and the products down the side. The numbers must follow the orders if they are corrected later.
Your task
- Look at the orders in A1:D15 and at the empty summary table in G2:I4. The regions are in G1:I1 and the products are in F2:F4.
- In G2, write one SUMIFS formula that totals the revenue for the region above it and the product beside it.
- Use $ signs so that the same formula is right in every cell of the table. Press Enter.
- Copy G2 (Ctrl+C) and paste it over G2:I4 (select the range, then Ctrl+V).
- Select Check Answer.
You are done when
- All nine cells in G2:I4 contain a formula and show the right revenue.
- Each formula reads its region from row 1 and its product from column F, instead of typed text.
- The data ranges cover all fourteen orders in every cell.
What you will practise
Total values that meet two conditions, and lock the right parts of each reference so a single formula can be copied across a whole table.
The idea in 30 seconds
SUMIFS adds up the numbers in one range for the rows that meet several conditions. The first argument is the range to add up; after that come pairs of a range to test and its condition. A row counts only when every pair is true. All the ranges must be the same size.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell G2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
SUMIFS adds up the numbers in one range for the rows that meet several conditions. The first argument is the range to add up; after that come pairs of a range to test and its condition. A row counts only when every pair is true. All the ranges must be the same size.
To build a summary table with one formula, the formula has to be copied in two directions. A dollar sign locks part of a reference: $B$2 never moves, B$2 keeps its row, and $B2 keeps its column. Lock the data ranges completely, keep the row for the headings that run across the top, and keep the column for the labels down the side. Then the same formula is right in every cell.
Skills you will practise
- SUMIFS
- Mixed references
- Absolute references
- Summary table
Topic guide: Excel SUMIF, COUNTIF and SUMIFS Practice
Complete first: Total Revenue for One Region
Common mistakes to avoid
- Ranges that are not locked
- Without $ the ranges slide down and across as you copy, so cells further from G2 add up the wrong rows, or empty columns.
- Locking everything
- If the region and product references are fully locked, every cell uses G1 and F2 and shows the same number.
- Using SUMIF for two conditions
- SUMIF can test only one range. Two conditions need SUMIFS, which puts the range to add first.