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. Pivot tables

GROUPBY practice: tickets per agent with a filter, largest first

  • Expert
  • ~9 min
  • Pivot tables
  • Excel 365

The service desk logged 220 tickets in October 2026 (A2:D221): ticket ID, opening date, the agent who handled it and its priority — Low, Normal, High or Urgent. The team lead wants the PivotTable she would build in Excel: Priority as the report filter set to High, Agent in Rows, Count of Ticket ID in Values, no grand total, and the busiest agent on top. This practice grid has no PivotTable command, so you build the report with one GROUPBY formula instead: the filter value sits in G1 and the report starts in F4, under the headings in F3:G3.

What you need to do

  1. 1G1 is the report filter (Priority = High), like the Filters area above a PivotTable. Click F4, where the report starts.
  2. 2Start with the counting part: GROUPBY(C2:C221,A2:A221,COUNTA, … — the agents are the row field, the ticket IDs are the values and COUNTA counts them, which is a PivotTable's "Count of Ticket ID". COUNT would return 0 for every agent, because ticket IDs are text.
  3. 3Add the optional arguments in their order: field_headers 0 (the ranges have no header row), total_depth 0 (no Total row), sort_order 1 (A to Z for now) and filter_array D2:D221=G1 (only the rows whose priority equals G1): =GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1).
  4. 4Sort by the count, largest first, by wrapping it in SORT: =SORT(GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),2,-1) — column 2 of the result, -1 for descending. Excel 365 can also sort inside GROUPBY with sort_order -2, but this practice grid ignores the column number in sort_order, so use SORT here; it works the same in Excel.
  5. 5Press Enter and keep the cells below and to the right of F4 empty, or the result shows #SPILL!. Type the 0 for total_depth rather than leaving it empty: in Excel an empty argument means "automatic", which brings the Total row back.
  6. 6Try the filter: change G1 to Urgent and watch the report re-rank itself, then put High back and click Check.

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 F4, write one formula with GROUPBY that counts the tickets (the ticket IDs in A2:A221) per agent (C2:C221) for the priority in G1 only, with no total row, sorted by the count from largest to smallest.

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 220-row dataset on one sheet, one answer cell, about 9 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • F4=SORT(GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),2,-1)

Each optional argument of GROUPBY is a PivotTable setting: COUNTA as the function is "Count of Ticket ID", total_depth 0 switches the grand total off, and filter_array D2:D221=G1 is the report filter — only the rows whose priority matches G1 take part. SORT(…,2,-1) then does what "Sort Largest to Smallest" does on the count column. Because the filter value lives in a cell, typing another priority into G1 re-filters and re-ranks the whole report at once.

The PivotTable route in Excel: Priority to Filters (pick High), Agent to Rows, Ticket ID to Values (a text field is counted: "Count of Ticket ID"), Design → Grand Totals → Off for Rows and Columns, then right-click a count → Sort → Sort Largest to Smallest. Pitfall 1: sorting a GROUPBY that still has its Total row sorts the Total in with the agents — it lands on top. Switch the totals off first (total_depth 0) or drop them with DROP(…,-1). Pitfall 2: an empty argument is not a zero. In Excel, GROUPBY(…,COUNTA,0,,…) means "automatic" totals and shows the Total row; type the 0. Pitfall 3: COUNT counts numbers only, so it returns 0 for text IDs; COUNTA counts every non-empty value. Pitfall 4: ties. In this grid SORT keeps tied rows in the order GROUPBY gave them (A to Z); if the order of ties matters, give SORTBY a second key, e.g. =LET(g,GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),SORTBY(g,CHOOSECOLS(g,2),-1,CHOOSECOLS(g,1),1)). Excel 365 can also sort inside GROUPBY: =GROUPBY(C2:C221,A2:A221,COUNTA,0,0,-2,D2:D221=G1) sorts by the 2nd column descending there, while this practice grid ignores the column number and sorts the names Z to A. Several conditions combine with *: (D2:D221=G1)*(B2:B221>=DATE(2026,10,15)). GROUPBY needs Excel for Microsoft 365.

Other ways that work

  • =SORT(GROUPBY(FILTER(C2:C221,D2:D221=G1),FILTER(A2:A221,D2:D221=G1),COUNTA,0,0),2,-1) — filter the rows first, then group
  • =LET(g,GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),SORTBY(g,CHOOSECOLS(g,2),-1)) — SORTBY on the count column
  • =GROUPBY(C2:C221,A2:A221,COUNTA,0,0,-2,D2:D221=G1) — Excel 365 only: sorts by the count inside GROUPBY (this practice grid ignores the column number in sort_order)

Common mistakes

Counting with COUNT instead of COUNTA: the ticket IDs are text, so every agent shows 0 and the ranking is meaningless; a PivotTable's "Count" of a text field is COUNTA.

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 20, 2026. Author: GridBelt team.

In these programs

  • Excel test in 7 days, Level 3 planDay 6
  • Excel for Data AnalystsDay 8

Related exercises

  • A pivot-style sales report by region with GROUPBYIntermediate, ~6 min
  • Products ranked by sales with SORTBYExpert, ~8 min
  • Orders per region and month with COUNTIFSIntermediate, ~7 min

Function reference

Official documentation

Next exercise

Share of revenue by channel: % of Grand Total with formulas