Difficulty
Beginner → Early Intermediate
Estimated Time
15–25 minutes
The Problem
You now have transaction-level sales data. In many real reporting scenarios, leadership does not want totals “by product only”—they want totals by product over time.
Your task is to build a simple month-based summary that answers:
- How much did each product sell per month?
- What is the monthly total across all products?
You will do this using formulas (no Pivot Table required), using a clean “report table” structure.
Dataset (Use This)
Create a worksheet named SalesData with these columns:
| Date | Product | Quantity | Unit Price |
|---|---|---|---|
| 2025-01-02 | Laptop | 2 | 900 |
| 2025-01-02 | Mouse | 5 | 20 |
| 2025-01-03 | Laptop | 1 | 900 |
| 2025-01-03 | Keyboard | 3 | 45 |
| 2025-01-15 | Mouse | 2 | 20 |
| 2025-02-02 | Laptop | 1 | 900 |
| 2025-02-05 | Mouse | 10 | 20 |
| 2025-02-08 | Keyboard | 2 | 45 |
| 2025-02-20 | Laptop | 1 | 900 |
| 2025-03-01 | Mouse | 4 | 20 |
| 2025-03-10 | Keyboard | 1 | 45 |
| 2025-03-22 | Laptop | 2 | 900 |
Your Task
Part A — Add a Calculated Column
- Add a Sales Amount column:
Sales Amount = Quantity * Unit Price
Part B — Create a “Month” Column (Key Step)
- Add a new column called Month that converts each Date into a month bucket (e.g.,
2025-01,2025-02,2025-03)
You may format it as:
Jan 2025or2025-01
Either is acceptable—just be consistent.
Part C — Build the Monthly Summary Table
Create a summary table that looks like this:
| Month | Laptop | Mouse | Keyboard | Total |
|---|---|---|---|---|
| 2025-01 | ? | ? | ? | ? |
| 2025-02 | ? | ? | ? | ? |
| 2025-03 | ? | ? | ? | ? |
Rules:
- Each cell should be computed using formulas
- The “Total” column sums across product columns for the month
Requirements
- Use formulas (Pivot Table is not required for this challenge)
- Your summary must update if new rows are added
- Use readable formulas (avoid hard-coded totals)
Expected Output (Totals)
If done correctly, you should get:
January 2025
- Laptop: 2700
- Mouse: 140
- Keyboard: 135
- Total: 2975
February 2025
- Laptop: 1800
- Mouse: 200
- Keyboard: 90
- Total: 2090
March 2025
- Laptop: 1800
- Mouse: 80
- Keyboard: 45
- Total: 1925
Hints (Optional)
- Use
SUMIFSto sum Sales Amount by Product and Month - A month bucket can be created with functions like
TEXT()orEOMONTH()depending on your approach - Keep your summary table clean—this is the foundation of dashboards later
Learning Objectives
By completing this challenge, you will learn:
- How to bucket dates into months
- How to build Pivot-style summaries with
SUMIFS - How to structure summary tables for dashboards and reporting
Next Challenge
Excel Challenge #3: Top Customers by Total Sales (Dynamic Ranking)
🔗 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 