SolveWithExcel

Excel Challenge #15: Date-Filtered Sales Summary (Start/End Date Controls)

Difficulty

Intermediate

Estimated Time

45–60 minutes

The Problem

Most business reporting is time-based. Stakeholders want answers like:

  • “What were sales last month?”
  • “How did we do in Q1?”
  • “Show me performance between two dates.”

In this challenge, you’ll build a date-filtered sales summary controlled by a Start Date and End Date, using formulas only—no Pivot Tables.

Dataset

Use a worksheet named SalesData with columns:

| Date | Region | Product | Sales |

(You can reuse the SalesData from Challenges #12–#14.)

Your Task

Part A — Create Date Controls

On a worksheet named Dashboard, add:

  • Start Date input (cell B2)
  • End Date input (cell B3)

Format both as dates.

Part B — Total Sales in Date Range

Calculate Total Sales between Start Date and End Date (inclusive) using SUMIFS.

Part C — Sales by Region (Date-Filtered)

Create a small table:

RegionSales (Date Range)
North
South
East
West

Each region value must respect the selected date range.

Part D — Sales by Product (Date-Filtered)

Create a second table:

ProductSales (Date Range)
Laptop Pro
Tablet Air
Phone Max

Part E — Top Region + Top Product

Add two callouts:

  • Top Region (highest date-filtered sales)
  • Top Product (highest date-filtered sales)

Formula-based only.

Requirements

  • Use formulas only (SUMIFS, MAX, XLOOKUP, etc.)
  • No Pivot Tables
  • All results must update instantly when date inputs change
  • Date filtering must be inclusive of Start and End dates

Learning Objectives

By completing this challenge, you will learn how to:

  • Filter results by date range using SUMIFS
  • Build date-driven dashboard controls
  • Produce dynamic “Top performer” callouts
  • Create reusable time-based reporting logic in Excel

Why This Matters

Date controls are foundational for:

  • Monthly / quarterly reporting
  • Rolling period analysis
  • Executive dashboards
  • Self-service analytics in Excel

Next Challenge

Excel Challenge #16: Rolling 30-Day Sales (Dynamic Window + Trend)

🔗 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.