Level 2Level 2 ProjectsProject, 10 min

A two-tab sales dashboard

The task

Raw deals live on the Data tab. The Summary tab is what your VP opens on Monday: pick a region and a date range in B3:B5, and every number below answers for that slice.
  1. Revenue for the sliceIn Summary!B7, add up Data's Amount where the region matches B3 and the date is between B4 and B5. That's three conditions: =SUMIFS(Data!E2:E15, Data!C2:C15, B3, Data!A2:A15, ">="&B4, Data!A2:A15, "<="&B5)
  2. Count the dealsIn Summary!B8, count the deals in the same slice with COUNTIFS. Same condition pairs, no sum range.
  3. Average deal sizeIn Summary!B9, average the deal amounts for the slice with AVERAGEIFS.
  4. Biggest dealIn Summary!B10, find the largest single deal in the slice with MAXIFS.
  5. Who closed itIn Summary!B11, look up the biggest deal's amount (B10) in Data's Amount column and return the rep with XLOOKUP.

The data, 2 tabs

The starting sheet. Cells outlined in green are where your formulas go.

Summary tab
AB
1Sales dashboard
2
3RegionWest
4FromMay 1, 2026
5ToMay 31, 2026
6
7Revenue
8Deals
9Avg deal
10Biggest deal
11Closed by
Data tab
ABCDE
1DateRepRegionCustomerAmount
2Apr 2, 2026MayaWestBean There Coffee$3,200
3Apr 6, 2026TheoEastNorthwind$1,850
4Apr 9, 2026PriyaCentralLettuce Eat$4,100
5Apr 14, 2026MayaWestSir Loin Steakhouse$7,600
6Apr 20, 2026JonahEastCobalt Labs$2,900
7Apr 27, 2026LenaWestFieldstone$5,300
8May 1, 2026TheoEastHarbor & Co$6,400
9May 5, 2026PriyaCentralCurl Up & Dye$2,250
10May 11, 2026MayaWestBean There Coffee$4,700
11May 15, 2026JonahEastQuillworks$8,150
12May 19, 2026LenaWestKnead to Know Bakery$3,600
13May 22, 2026PriyaCentralLettuce Eat$5,900
14May 28, 2026TheoEastCobalt Labs$1,400
15May 29, 2026LenaWestFieldstone$6,800

See all lessons