FormulaGym
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 with two conditionsComing soon
  3. 3
    Count with wildcardsComing soon
  4. 4
    Count dates in a rangeComing soon
  5. 5
    Duplicate finderComing soon
  6. 6
    Dashboard countsComing soon

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