Challenge Scenario
In retail and sales tracking, you often receive a long list of customer sales records. To understand which areas are performing best, you need to summarize total sales and count orders for each region.
As a Sales Assistant, you have a list of sales in Columns A to C. Your task is to fill in the summary table in Columns E to G:
- Total Revenue (Column F): Calculate total sales for each region using
SUMIF.
- Transaction Count (Column G): Count how many sales happened in each region using
COUNTIF.
- Grand Totals (Row 6): Calculate total revenue (Cell F6) and total sales count (Cell G6) across all regions using
SUM.
Your Sheet Layout
Let's take a quick look at how your sheet is organized before writing formulas:
- Sales List (Columns A to C): Your original raw list showing each transaction's ID, Region (North, South, East), and sales Amount ($).
- Regional Summary Table (Columns E to G): Where you calculate Total Revenue and Transaction Count for each region.
- Total Row (Row 6): Where you sum up all regions to get the final grand total.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:A7 |
Tx ID |
Sales List |
Transaction ID (TX-01 to TX-06) |
The tracking code for each sale. |
B2:B7 |
Region |
Sales List |
Region names (North, South, East) |
The region for each sale. Lock this range with dollar signs ($B$2:$B$7). |
C2:C7 |
Amount ($) |
Sales List |
Dollar amounts ($300 to $700) |
The sale amount. Lock this range with dollar signs ($C$2:$C$7). |
E2:E4 |
Region |
Summary Table |
Region names (North, South, East) |
The region name you are looking for in each row. |
F2:F4 |
Total Revenue |
Summary Table |
=SUMIF($B$2:$B$7, E2, $C$2:$C$7) |
Adds up all sales amounts for the matching region. |
G2:G4 |
Transaction Count |
Summary Table |
=COUNTIF($B$2:$B$7, E2) |
Counts how many sales happened in that region. |
F6 |
Total Revenue |
Grand Total |
=SUM(F2:F4) |
Adds up the total revenue from all regions ($3,050). |
G6 |
Total Transactions |
Grand Total |
=SUM(G2:G4) |
Adds up the total number of sales (6). |
Note: Column D is left empty on purpose to give visual space between the sales list and the summary table.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column F (F2:F4), calculate Total Revenue for each Region using SUMIF (e.g. =SUMIF($B$2:$B$7, E2, $C$2:$C$7)).
2
In Column G (G2:G4), count transactions per Region using COUNTIF (e.g. =COUNTIF($B$2:$B$7, E2)).
3
In cell F6, calculate total revenue across all regions using SUM.
4
In cell G6, calculate total transaction count using SUM.
Solution & Formula Breakdown
Here is how the formulas work in simple terms:
1. Adding up Sales by Region with SUMIF (F2:F4)
=SUMIF($B$2:$B$7, E2, $C$2:$C$7)
Think of SUMIF as asking Excel 3 simple questions:
- Where to look? In the Region column (
$B$2:$B$7).
- What to find? The region name in this row (
E2, which is "North").
- What to add together? The numbers in the Amount column (
$C$2:$C$7).
For the North region, Excel finds 3 sales ($450 + $700 + $650) and adds them up to get $1,800.
2. Counting Sales by Region with COUNTIF (G2:G4)
=COUNTIF($B$2:$B$7, E2)
COUNTIF counts how many times the word in E2 appears in the Region list ($B$2:$B$7).
3. Calculating Grand Totals (F6 & G6)
- Cell F6 (Total Revenue):
=SUM(F2:F4) → Gives $3,050.
- Cell G6 (Total Transactions):
=SUM(G2:G4) → Gives 6.