Lessons Lesson 35

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.

By the end you can

  • combine SUM, SUMIF, COUNTA, IF and VLOOKUP on one sheet.
  • pick the right conditional function for a question without being told.

Practises SUM COUNTA VLOOKUP SUMIF IF

By Ali, Founder, Excel Lessons

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.

Read more about this

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.

Type a formula

A week of bookshop orders, with a price list beside them.

In E2, type:

=SUM(D2:D7)

That totals the copies across all six orders.

E2
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

  1. 1

    A week of bookshop orders, with a price list beside them. In E2, type: =SUM(D2:D7). That totals the copies across all six orders.

    Hint. SUM of D2:D7.

  2. 2

    Read =COUNTA(A2:A7) and say what it returns. That counts the orders in column A.

    Hint. Six rows.

  3. 3

    In E4, look up O-101's price in F2:G5. Use an exact-match VLOOKUP on the code in B2.

    Hint. B2, F2:G5, 2, FALSE.

  4. 4

    In E5, total the Fiction copies with SUMIF.

    Hint. Test column C for Fiction, then total the copies.

  5. 5

    Fiction is meant to clear 8 copies a week. In E6, show On target if E5 reached it. Otherwise, show Below.

    Hint. IF on E5.

  6. 6

    In E7, work out what order O-104 is worth. That's copies in D5 times the title's price.

    Hint. D5 times the lookup.