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. COUNTIF and COUNTIFS

COUNTIF practice: orders per status on a 200-row export

  • Beginner
  • ~5 min
  • COUNTIF and COUNTIFS

The web shop's order export for September–October lists 200 orders with a Status column: Delivered, Shipped, Processing or Cancelled. Customer service wants a small status board — how many orders are in each state — that keeps updating as the export is refreshed. The four status names are already typed in G2:G5; put the counts next to them.

What you need to do

  1. 1Click H2. The status for this summary row is in G2 ("Delivered"); the statuses of all orders are in D2:D201.
  2. 2Write COUNTIF with the range first and the criterion second: the range is D2:D201, the criterion is the cell G2 — not the typed word. Lock the range with $ (select it and press F4 on Windows, ⌘T on Mac) so it stays put when you fill down.
  3. 3Press Enter and fill H2 down to H5 (drag the fill handle, or select H2:H5 and press Ctrl+D / ⌘D).
  4. 4The four counts must add up to 200. 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 H2:H5, use COUNTIF to count how many orders in D2:D201 have the status named in column G of the same row — one formula in H2, 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 200-row dataset on one sheet, one answer cell, about 5 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • H2=COUNTIF($D$2:$D$201,G2)

COUNTIF(range, criterion) counts the cells in the range that match the criterion. With the range locked as $D$2:$D$201 and the criterion pointing at the label in column G, one formula filled down produces all four counts, and the board updates when the export is refreshed. Text criteria are matched case-insensitively, so "delivered" would count the same rows as "Delivered".

Pitfall 1: filling =COUNTIF(D2:D201,G2) down without $ shifts the range to D3:D202, D4:D203 … — the counts drift by a row each time and the totals no longer add up to 200. Pitfall 2: typing the status into the formula (=COUNTIF($D$2:$D$201,"Delivered")) works but must be edited in four places and breaks the fill-down; referencing G2 keeps the board data-driven. Pitfall 3: exports with trailing spaces or "Canceled" vs "Cancelled" create silent misses — a quick SUM of the counts against the row count catches them. Alternatives: COUNTIFS($D$2:$D$201,G2) is COUNTIF with room for more conditions (status and month, next exercise); SUMPRODUCT(($D$2:$D$201=G2)*1) is the array form; in Excel 365, =COUNTIF($D$2:$D$201,G2:G5) spills all four counts from one cell. Whole-column references (D:D) are acceptable here because column D holds only statuses and a header; avoid them inside SUMPRODUCT, which would evaluate a million rows.

Other ways that work

  • =COUNTIFS($D$2:$D$201,G2) — same count with the multi-criteria function
  • =SUMPRODUCT(($D$2:$D$201=G2)*1) — array-style count without COUNTIF (not accepted here, because this task asks for COUNTIF)
  • =COUNTIF(D:D,G2) — whole-column reference; fine while column D holds nothing but statuses

Common mistakes

Filling =COUNTIF(D2:D201,G2) down without locking the range: H3 counts D3:D202, H4 counts D4:D203, and the four counts no longer add up to 200.

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 test in 7 days, Level 1 planDay 3
  • Excel for Administrative AssistantsDay 2
  • Excel for Sales and MarketingDay 1

Related exercises

  • Count .edu emails with a wildcardBeginner, ~4 min
  • Orders per region and month with COUNTIFSIntermediate, ~7 min
  • Sales by region with SUMIFBeginner, ~5 min

Function reference

Official documentation

Next exercise

Count .edu emails with a wildcard