Level 1Summarizing DataProject, 8 min

A sales dashboard that answers questions

The task

Your manager has five questions about last quarter's 50 orders. Answer each with one formula, and the answers stay right when new orders arrive.
  1. Total revenueIn H2, add up every order's Amount.
  2. How many big ordersIn H3, count the orders over $500. Comparisons go in quotes: ">500".
  3. Average Enterprise dealIn H4, average the Amount for Enterprise orders only, with AVERAGEIF. It works just like SUMIF.
  4. One rep, one regionIn H5, total Priya's sales in the West. Two conditions means SUMIFS: what to add first, then each range and its match.
  5. Who closed the biggest dealIn H6, name the rep behind the largest single order.

The data

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

ABCDEFGH
1OrderRepRegionProductAmountQuestionAnswer
23001TheoWestStarter$150Total revenue
33002PriyaEastEnterprise$1,515Orders over $500
43003TheoCentralPro$345Avg Enterprise deal
53004PriyaCentralStarter$140Priya's West sales
63005TheoCentralStarter$95Rep with the biggest deal
73006PriyaWestPro$450
83007MayaWestStarter$205
93008MayaEastPro$510
103009MayaEastEnterprise$1,685
113010LenaWestEnterprise$1,380
123011LenaWestPro$545
133012LenaCentralStarter$210
143013PriyaWestEnterprise$2,100
153014LenaCentralPro$250
163015MayaEastStarter$220
173016JonahEastEnterprise$1,725
183017LenaCentralEnterprise$1,550
193018PriyaWestPro$550
203019LenaWestStarter$90
213020TheoEastStarter$170
223021MayaEastStarter$140
233022MayaCentralPro$590
243023PriyaEastEnterprise$2,060
253024PriyaWestEnterprise$1,870
263025LenaEastPro$340
273026MayaCentralEnterprise$1,120
283027LenaEastPro$350
293028PriyaWestEnterprise$1,625
303029LenaWestStarter$210
313030LenaEastPro$370
323031MayaEastStarter$85
333032TheoEastStarter$210
343033JonahWestEnterprise$1,640
353034TheoWestPro$430
363035PriyaWestPro$475
373036MayaEastPro$435
383037PriyaWestEnterprise$1,645
393038MayaEastPro$380
403039PriyaCentralStarter$110
413040LenaCentralStarter$105
423041TheoWestPro$330
433042JonahEastStarter$225
443043PriyaCentralPro$435
453044JonahWestStarter$205
463045JonahWestPro$425
473046LenaWestEnterprise$2,365
483047PriyaCentralStarter$140
493048MayaEastPro$420
503049TheoWestPro$615
513050LenaWestPro$605

See all lessons