Skip to content

Exercise 34 of 41 · Conditional calculations · Advanced · 12 min

Sales Between Two Dates with SUMIFS

Your task: In F4:F6, report total sales, East sales and the order count for the dates in F1 and F2.

Full instructions, scenario and goal

Scenario

A regional sales manager prepares a monthly report from a list of orders. The reporting window is set in two input cells, so she can reuse the sheet every month. For the current window she needs the total sales, the East region's sales, and how many orders were placed.

Your task

  1. The report window is in F1 (start) and F2 (end). Both dates are included in the window.
  2. In F4, find the total sales of all orders inside the window.
  3. In F5, find the sales of East orders inside the window.
  4. In F6, count the orders inside the window.
  5. Use the cells F1 and F2 in your formulas, then select Check Answer.

You are done when

  • Orders on the start date and on the end date are included.
  • The window comes from F1 and F2, not from typed dates.
  • The formulas cover all twelve order rows.

What you will practise

Build comparison criteria from cell values with &, so a report window can change without rewriting the formulas.

The idea in 30 seconds

SUMIFS and COUNTIFS accept comparison criteria as text, such as ">=45689". To compare against a date stored in a cell, join the operator and the cell with &: ">="&F1 produces the same criterion, built from whatever date is in F1. A date is just a number to Excel, so this works for dates exactly like it does for amounts. Use two criteria on the same date column to describe a window: on or after the start, and on or before the end.

Read the full explanation below the sheet

Spreadsheet

F4
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
10
11
12
13

Loading spreadsheet…

Keyboard shortcuts
Arrow keys
Move between cells
Shift + Arrow keys, or drag with the mouse
Select a range
Ctrl + Arrow keys
Jump to the edge of a block of data
Type, or F2, or double-click
Edit a cell (F2 switches arrow keys between moving and editing)
Click or arrow keys inside a formula
Insert a cell reference after =, +, (, comma and similar
F4
Cycle a reference through A1, $A$1, A$1, $A1
Enter / Tab while editing
Confirm and move down / right (Shift reverses)
Esc
Cancel the edit
Ctrl + C / X / V
Copy, cut and paste (formulas adjust like in Excel)
Delete
Clear the selected cells
Ctrl + Z / Ctrl + Y
Undo and redo
Ctrl + Enter
Check your answer
Tab when not editing
Leave the spreadsheet

Type your answer in cell F4. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.

New here? How an exercise works
  1. Read the task. Highlighted cells are yours to fill in; the rest are locked.
  2. Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
  3. Press Check answer. Stuck? Reveal hints one at a time.

The full explanation: the idea behind this exercise

SUMIFS and COUNTIFS accept comparison criteria as text, such as ">=45689". To compare against a date stored in a cell, join the operator and the cell with &: ">="&F1 produces the same criterion, built from whatever date is in F1. A date is just a number to Excel, so this works for dates exactly like it does for amounts. Use two criteria on the same date column to describe a window: on or after the start, and on or before the end.

Reports like this should never contain typed dates. Put the start and end in input cells, point the criteria at them, and the same formulas answer next month's question after you change two cells. Mind the boundaries: orders on the start date and the end date belong to the report, so use >= and <=, not > and <. The criteria for text conditions, such as the region, can sit in the same formula as extra pairs of range and criterion.

Skills you will practise

  • SUMIFS
  • COUNTIFS
  • Date criteria
  • Concatenation
  • Reporting

Topic guide: Excel SUMIF, COUNTIF and SUMIFS Practice

Complete first: Subscription Renewal Dates with EDATE and EOMONTH, Build a Region by Product Sales Summary with SUMIFS

Common mistakes to avoid

Excluding the boundary dates
">" and "<" leave out orders placed on the first and last day of the window.
Typing the dates into the formula
The report would need rewriting every month. Join the operator with the date cell instead.