Lessons Lesson 100

Project: A Sales Report

About this lesson

A sales report is three things. A row-level total filled down. A summary block of SUMIFS with locked data ranges and unlocked labels. And a lookup that turns the biggest number into a name.

By the end you can

  • build a report with row totals, a summary block and a top-rep lookup.
  • lock the right ranges so a summary block fills down correctly.

Practises SUMIFS INDEX MATCH MAX COUNTIFS

By Ali, Founder, Excel Lessons

The idea

The first project is the report every team has. The row total fills down. The summary block reads the labels beside it, so one SUMIFS serves every region. That works only if the data ranges are locked and the label is not, which is the mistake step 3 repairs. The best rep is MAX inside MATCH inside INDEX. The share is the part over the whole.

Nothing here is new. The point of a project is the order. Row numbers first, then the summaries that read them, then the one-line answers a manager will ask for.

The mistake to watch for

A summary block whose ranges moved. SUMIFS copied down without the dollar signs still totals something, so the wrong number looks right. Lock the data ranges and leave the label relative. Then check that the region totals add up to the grand total.

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

In E2, type:

=C2+D2

This fills down to E7.

E2
Row A B C D E F G
1 Region Rep Q1 Q2 Year
2 North Ada 120 135
3 South Ben 98 110
4 North Cai 143 128
5 East Dee 76 88
6 South Eli 130 142
7 North Fay 88 95
8 North
9 South
10 Best rep
11 North share
12 North over 200

Every step

  1. 1

    In E2, type: =C2+D2. This fills down to E7.

    Hint. Two cells added.

  2. 2

    In B8, total the North year with SUMIFS. Shape: =SUMIFS(range, range, cell). Remember: dollar signs lock a range so it doesn't move.

    Hint. Lock the ranges, not the label.

  3. 3

    B9 was copied without dollar signs, so its ranges moved. It now reads the label rows as data too. Fix B9.

    Hint. Dollar signs on the two ranges.

  4. 4

    MATCH finds where a value sits in a range. In B10, name the rep with the highest year. Shape: =INDEX(range, MATCH(MAX(range), range, 0)).

    Hint. INDEX, MATCH, MAX, from the outside in.

  5. 5

    Read =B8/SUM(E2:E7) and say North's share, as a fraction.

    Hint. 709 over the grand total.

  6. 6

    In B12, count the North reps whose year is over 200 with COUNTIFS. Shape: =COUNTIFS(range, text, range, ">n").

    Hint. "North" and ">200".