Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. SUMIF and SUMIFS

AVERAGEIFS and MAXIFS practice: average and largest won deal per rep (180 deals)

  • Intermediate
  • ~8 min
  • SUMIF and SUMIFS

The CRM export holds 180 deals from April to September 2026 for five account executives, with the stage (Won, Lost or Open) and the deal amount. For the quarterly review the sales director wants two numbers per rep, counting won deals only: the average deal size and the largest deal closed. The rep names are already listed in H2:H6; fill the two columns next to them with formulas that read the stage, so open and lost deals never leak into the figures.

What you need to do

  1. 1Click I2. The rep for this row is in H2; the deal amounts are in F2:F181, the reps in B2:B181 and the stages in E2:E181.
  2. 2Write AVERAGEIFS with the average range first (F2:F181), then two pairs of range, criterion: B2:B181 against H2 and E2:E181 against "Won". Lock the three ranges with $ (F4 on Windows, ⌘T on Mac).
  3. 3Click J2 and write MAXIFS with exactly the same arguments — max range first, then the same two pairs.
  4. 4Fill I2 down to I6 and J2 down to J6 (select I2:J6 and press Ctrl+D / ⌘D after filling the first row). Every average must be smaller than the largest deal on its row. Then click Check (Ctrl+Enter / ⌘+Return).

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedIn I2:I6, use AVERAGEIFS to show the average amount (F2:F181) of the deals whose rep in B2:B181 is the name in column H and whose stage in E2:E181 is "Won" — one formula in I2, filled down.
  2. Not checkedIn J2:J6, use MAXIFS with the same two conditions to show each rep's largest won deal — one formula in J2, filled down.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 180-row dataset on one sheet, 2 answer cells, about 8 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • I2=AVERAGEIFS($F$2:$F$181,$B$2:$B$181,H2,$E$2:$E$181,"Won")
  • J2=MAXIFS($F$2:$F$181,$B$2:$B$181,H2,$E$2:$E$181,"Won")

AVERAGEIFS and MAXIFS belong to the same family as SUMIFS: the range to summarise comes first, followed by pairs of range and criterion that every counted row must satisfy. Here the pairs are "rep equals the name in column H" and "stage equals Won", so lost and open deals are excluded from both the average and the maximum. With the data ranges locked and the rep reference left relative, one formula per column filled down covers all five reps.

Pitfall 1: AVERAGEIFS returns #DIV/0! when no row matches (a rep with no won deals yet); wrap it in IFERROR(…,0) or IFERROR(…,"–") on live dashboards. MAXIFS returns 0 in the same situation, which can look like a real value. Pitfall 2: averaging the whole pipeline (AVERAGEIF on the rep only) mixes open and lost deals into "average deal size" — the stage condition is what makes the metric meaningful. Pitfall 3: MAXIFS and MINIFS need Excel 2019 or 365; in Excel 2016 the equivalent is the array formula MAX(IF((B2:B181=H2)*(E2:E181="Won"),F2:F181)) entered with Ctrl+Shift+Enter, or AGGREGATE(14,6,F2:F181/((B2:B181=H2)*(E2:E181="Won")),1). Alternatives: SUMIFS(...)/COUNTIFS(...) reproduces the average and makes the deal count visible; LARGE(IF(...),1) is the general form for the second- or third-largest deal. Reading the result: the gap between average and largest deal shows how dependent a rep is on one big win — add COUNTIFS for the number of won deals to complete the review.

Other ways that work

  • =SUMIFS($F$2:$F$181,$B$2:$B$181,H2,$E$2:$E$181,"Won")/COUNTIFS($B$2:$B$181,H2,$E$2:$E$181,"Won") — average as sum ÷ count
  • =AGGREGATE(14,6,$F$2:$F$181/(($B$2:$B$181=H2)*($E$2:$E$181="Won")),1) — largest value without MAXIFS (Excel 2010+)
  • =MAX(IF(($B$2:$B$181=H2)*($E$2:$E$181="Won"),$F$2:$F$181)) — array formula; Ctrl+Shift+Enter before Excel 365

Common mistakes

Leaving out the stage condition (AVERAGEIFS($F$2:$F$181,$B$2:$B$181,H2)): the figures include lost and open deals, so every average is off and the grader names the first rep whose numbers differ.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel for Data AnalystsDay 2
  • Excel for Sales and MarketingDay 3

Related exercises

  • Sales by region and product with SUMIFSIntermediate, ~7 min
  • Sales by region with SUMIFBeginner, ~5 min
  • Orders per region and month with COUNTIFSIntermediate, ~7 min

Function reference

Official documentation

Next exercise

Weighted average price with SUMPRODUCT