Difficulty
Intermediate
Estimated Time
35–45 minutes
The Problem
A common business question is not just “How much did we sell?” but:
- Which regions are performing best?
- Which products are driving that performance?
- What share of total sales does each region represent?
In this challenge, you’ll build a region-by-product performance matrix using SUMIFS, then add share-of-total analysis to make the results dashboard-ready.
Dataset
Create a worksheet named SalesData with the following table:
| Date | Region | Product | Sales |
|---|---|---|---|
| 2025-01-05 | North | Laptop Pro | 4200 |
| 2025-01-12 | North | Phone Max | 5100 |
| 2025-01-20 | South | Laptop Pro | 3600 |
| 2025-02-03 | East | Tablet Air | 2200 |
| 2025-02-11 | West | Phone Max | 4800 |
| 2025-02-18 | South | Tablet Air | 1900 |
| 2025-03-02 | East | Laptop Pro | 4100 |
| 2025-03-10 | West | Tablet Air | 2050 |
| 2025-03-19 | North | Tablet Air | 2450 |
| 2025-03-25 | South | Phone Max | 5300 |
| 2025-04-04 | East | Phone Max | 4950 |
| 2025-04-12 | West | Laptop Pro | 3850 |
Make sure Date is stored as a real date.
Your Task
Part A — Build the Performance Matrix
Create a new worksheet named Matrix.
- In A2:A5, list regions:
- North
- South
- East
- West
- In B1:D1, list products:
- Laptop Pro
- Tablet Air
- Phone Max
- Fill the matrix so each cell returns:
- Total Sales for that Region + Product
Use SUMIFS().
Part B — Row and Column Totals
- Add:
- Row totals (total sales per region)
- Column totals (total sales per product)
- A Grand Total
All totals must be formula-based.
Part C — Share of Total (Region %)
- Add a column named Region Share %:
Region Share % = Region Total / Grand Total
Format as a percentage.
Part D — Best Region (Optional)
- Add a cell that returns the Top Region by total sales.
(Keep it formula-driven.)
Requirements
- Use formulas only (
SUMIFS,SUM, etc.) - No Pivot Tables
- Results must update automatically if SalesData changes
Learning Objectives
By completing this challenge, you will learn how to:
- Build a cross-tab performance matrix using
SUMIFS - Summarize results with totals and grand totals
- Add share-of-total analysis for decision-making
- Prepare data in a dashboard-friendly layout
Why This Matters
This pattern appears in:
- Regional sales reporting
- Product performance breakdowns
- Business reviews and leadership updates
- “Heatmap-style” Excel dashboards (next step)
Next Challenge
Excel Challenge #13: Heatmap Sales Matrix (Conditional Formatting)
🔗 View reference solution on GitHub
(After you’ve tried the challenge)
Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.
SolveWithExcel 