The Formula Gym
XP 0
Streak 0

The COUNTIF function in Excel

COUNTIF counts the cells in a range that meet one condition.

What COUNTIF does

The condition can be text in quotes, a number, or a comparison such as ">100". The match ignores upper and lower case. Use it to answer questions like how many orders are shipped, or how many people work in one department.

Syntax

=COUNTIF(range, criteria)
range
The cells to look through.
criteria
The condition to match, such as "Shipped", 5 or ">100". Text and comparisons go in quotes.

Worked example

AB
1OrderStatus
21001Shipped
31002Pending
41003Shipped
51004Shipped
61005Pending
7Shipped3
B7 =COUNTIF(B2:B6,"Shipped")

Three of the five rows say Shipped, so COUNTIF returns 3.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Sales & Ops

Count orders by status, such as Shipped or Late.

HR

Count how many people are in one department.

Data cleanup

Count how often an entry appears to find duplicates.

Common mistakes

  • Leaving the quotes off a comparison. It must be ">100" in quotes, not >100.
  • Stray spaces. "North " with a trailing space does not match "North".
  • Trying to test two conditions at once. COUNTIF takes one. Use COUNTIFS for more.

The COUNTIF course

  1. 1
    Count what matchesFree practice below
    Practise
  2. 2
    Count between two valuesMembers lesson
  3. 3
    Count with wildcardsMembers lesson
  4. 4
    Count against a target cellMembers lesson
  5. 5
    Duplicate finderMembers lesson
  6. 6
    Dashboard countsMembers lesson

Common questions

Is COUNTIF case-sensitive?

No. "north" and "North" match each other.

Can COUNTIF use wildcards?

Yes. An asterisk matches any run of characters and a question mark matches a single character. For example "A*" counts entries that start with A.

Related functions

Unlock every lesson

Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.

See membership