Total Revenue for One Region
Your task: In H2, write a formula that totals the revenue of the East orders.
Full instructions, scenario and goal
Scenario
You work in sales operations for a company that sells computer equipment. The sheet lists twelve recent orders with the sales region and the revenue for each. Your manager asks: "How much revenue did the East region bring in?"
Your task
- Look at the order list in A1:E13. Each row is one order, with its sales region (column B) and revenue (column E).
- Select cell H2, next to the label "East region revenue".
- Type a formula that adds up the Revenue for the orders whose Region is East, then press Enter.
- Select Check Answer to see whether your formula is correct.
You are done when
- H2 contains a formula, not a number you typed.
- The formula returns the total revenue of the East orders.
- The formula would still be right if the orders were re-sorted or the revenue figures changed.
What you will practise
Add up only the values that meet a condition, using a formula that reads the data instead of repeating a number you worked out by hand.
The idea in 30 seconds
SUMIF adds up the numbers in one range, but only for the rows where a second range meets a condition. It needs three pieces: the range to test, the condition, and the range that holds the numbers to add. The test range and the sum range line up row by row, so they should be the same size and start on the same row.
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 H2. 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
SUMIF adds up the numbers in one range, but only for the rows where a second range meets a condition. It needs three pieces: the range to test, the condition, and the range that holds the numbers to add. The test range and the sum range line up row by row, so they should be the same size and start on the same row.
The condition can be a word such as "North", a number, or a comparison written in quotation marks, such as ">100". Text conditions are not case sensitive. Typical uses are totalling the expenses of one category, the sales of one salesperson, or the orders from one region, and the result updates by itself as the data changes.
Skills you will practise
- SUMIF
- Conditional sum
Common mistakes to avoid
- Forgetting the quotation marks
- A text condition such as East must be written in quotation marks inside the formula: "East". Without them Excel looks for a named item called East and shows #NAME?.
- Adding every row
- SUM(E2:E13) adds all twelve orders. SUMIF only adds the rows where the condition is true.
- Typing the total you worked out
- Adding the East orders by hand and typing the result gives the right number today but is wrong as soon as the data changes. The formula should do the work.