Putting It Together
About this lesson
One sheet can need a plain total, a count, a lookup, a conditional total and an IF. The skill is knowing which question each one answers.
- combine SUM, SUMIF, COUNTA, IF and VLOOKUP on one sheet.
- pick the right conditional function for a question without being told.
The idea
Everything from the first seven modules on one sheet. The plain total comes first, so the conditional totals can be checked against it. The lookup fetches a price. The SUMIF totals a category. The IF turns that total into a verdict. The last step puts a lookup inside a multiplication, which is how most real order sheets are priced.
The habit to leave with: when a number on a report looks wrong, find the plain total it should be part of. If the parts do not add up to the whole, a condition is missing rows.
The mistake to watch for
Using a lookup when the key repeats. A lookup returns the first match and never says there was a second. When the question is really about every row in a category, it is SUMIF or COUNTIF. Decide whether you want one row or all of them before you pick the function.
Where this comes up again
This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.
A week of orders at a bookshop, with a price list beside them.
In E2, type =SUM(D2:D7) to total the copies ordered across all six orders.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Order | Title code | Category | Copies | Report | Code | Price |
| 2 | O-101 | BK-04 | Fiction | 3 | BK-01 | 22 | |
| 3 | O-102 | BK-01 | Cookery | 1 | BK-02 | 8.5 | |
| 4 | O-103 | BK-04 | Fiction | 2 | BK-03 | 14 | |
| 5 | O-104 | BK-02 | Children | 6 | BK-04 | 11.5 | |
| 6 | O-105 | BK-03 | Fiction | 4 | |||
| 7 | O-106 | BK-01 | Cookery | 2 | |||
| 8 |
Every step
-
A week of orders at a bookshop, with a price list beside them. In
E2, type=SUM(D2:D7)to total the copies ordered across all six orders. -
Read
=COUNTA(A2:A7)and say what it returns: the number of orders, from the Order column. -
In
E4, fetch the price of the book in order O-101 from the price list inF2:G5. Use an exact-matchVLOOKUPon the title code inB2. -
In
E5, total the copies ordered in the Fiction category withSUMIF. -
Fiction is meant to clear eight copies a week. In
E6, show On target if the figure inE5reached it and Below if it did not. -
In
E7, write a formula usingVLOOKUPthat works out what order O-104 is worth. That is its copies inD5, multiplied by the price of its title from the list.