Cellwise

Counting function

Excel COUNTIF function

COUNTIF counts the cells in a range that meet one condition. Use it to count orders from one region, scores above a pass mark, or tasks marked "Done". For more than one condition, use COUNTIFS.

fx=COUNTIF(range, criteria)

Arguments

range
The cells to check.
criteria
The condition, like "Done", 50, ">=70" or "A*". Text and operators go in quotes.

COUNTIF examples

Every result below was calculated live from this sample sheet.

ABCD
1TaskOwnerStatusHours
2Write briefKimDone3
3Design logoRajIn progress8
4Draft copyKimDone5
5Build pageAlexNot started12
6QA reviewRajDone2
7Launch emailKimIn progress4
  1. fx=COUNTIF(C2:C7,"Done")

    Result: 3

    How many tasks are finished. The match is not case-sensitive.

  2. fx=COUNTIF(D2:D7,">=5")

    Result: 3

    Put the operator and number in quotes to count tasks of 5 hours or more.

  3. fx=COUNTIF(C2:C7,"<>Done")

    Result: 3

    "<>" means not equal to, so this counts everything still open.

  4. fx=COUNTIF(A2:A7,"D*")

    Result: 2

    The * wildcard matches any characters, so this counts tasks starting with D.

How to use COUNTIF

  1. Click an empty cell.
  2. Type =COUNTIF( and select the range to check.
  3. Type a comma and the condition in quotes, like "Done".
  4. Close the bracket and press Enter.

Common COUNTIF mistakes

Operator outside the quotes

Write ">=5", not >="5" or >=5. The whole condition is one text string.

Comparing to a cell value

Join the operator to the cell with &: COUNTIF(D2:D7,">"&F1). Writing ">F1" looks for the text F1.

Extra spaces in the data

"Done " with a trailing space does not match "Done". Clean the data with TRIM or use "Done*".

COUNTIF FAQ

How do I count cells with specific text in Excel?

Use =COUNTIF(range,"text"). To count cells that contain the text anywhere, use wildcards: =COUNTIF(range,"*text*").

How do I use COUNTIF with multiple criteria?

Use COUNTIFS, which takes pairs of ranges and conditions. For an OR condition, add two COUNTIFs together.

Is COUNTIF case-sensitive?

No. "done", "Done" and "DONE" all match. Use SUMPRODUCT with EXACT if you need a case-sensitive count.

Why is my COUNTIF returning 0?

Common causes are extra spaces, numbers stored as text, or the operator placed outside the quotes. Test one cell with = to see if it really matches.

Practice COUNTIF in these lessons

Guides that use COUNTIF

Related functions

Updated 2026-10-31