How to Use COUNTIF in Excel (With Criteria Examples)

Excel COUNTIF counts how many cells in a range match one condition: "Open", exactly 2, 8 or more, anything but "Low". You write =COUNTIF(range, criteria), and the only tricky part is how to write the criteria. This guide is for anyone who has to answer "how many?" questions in a spreadsheet or a job test, and it shows every common type of criteria on one small table.

What does COUNTIF do in Excel?#

COUNTIF looks at each cell in a range and counts the ones that meet your condition. The condition is called the criteria. The syntax has two parts:

=COUNTIF(range, criteria)

COUNTIF works in every version of Excel. It is not case-sensitive, so "open" and "OPEN" count the same.

All the examples below use this support-ticket table in A1:E9:

How to use COUNTIF: step by step#

To count matching cells, select an empty cell, type the formula with the range first and the criteria second, and press Enter. Here is how to count the open tickets:

1. Click an empty cell, for example H2.
2. Type =COUNTIF( and select the Status column, D2:D9.
3. Type a comma, then "Open" in double quotes.
4. Close the bracket and press Enter.

=COUNTIF(D2:D9,"Open")

The result is 3 (T-1001, T-1005 and T-1007). Text criteria always need double quotes. Without them, Excel reads Open as a defined name and shows #NAME?.

COUNTIF criteria examples#

The criteria can be text, a number, a comparison, a wildcard or a cell. This table shows the most useful forms, all on the ticket data:

A few rules make all of these work:

• Numbers on their own need no quotes: 2.
• Operators go inside the quotes, touching the number: ">=8". Don't add a space.
• Not equal to is <> in Excel. The != from programming doesn't work, and neither does =>.
• Wildcards match part of the text: * is any number of characters and ? is exactly one. See how wildcards work in COUNTIF and SUMIF for more.

How to use a cell as the criteria#

Point the criteria at a cell when you want one formula to answer several questions. Say G8:G10 hold the agent names Wei, Sara and Jamal. In H8, type:

=COUNTIF($B$2:$B$9,G8)

Then select H8:H10 and fill down (Windows: Ctrl+D; Mac: ⌘D). You get 2 for Wei, 3 for Sara and 3 for Jamal.

The $ signs lock the range so it stays on rows 2 to 9 as you fill. G8 has no $, so it changes to G9 and G10. Don't put G8 in quotes: "G8" means the letters G and 8, not the name in the cell.

To combine an operator with a cell, keep the operator in quotes and join the cell with &. If H1 holds 5:

=COUNTIF(E2:E9,">"&H1)

This counts tickets over 5 hours: 3 (8, 12 and 6). Change H1 and the answer updates. Writing ">H1" would not work, because inside quotes H1 is just text.

How to count blank and non-blank cells with COUNTIF#

Use "" to count empty cells and "<>" to count cells that are not empty. For example, =COUNTIF(D2:D9,"") counts tickets with no status, and =COUNTIF(D2:D9,"<>") counts tickets that have one. A cell that only looks empty, because it holds a space, is not blank to Excel. If the numbers look off, check for hidden spaces.

For a plain count of filled cells, COUNTA is shorter. The article on COUNT vs COUNTA vs COUNTBLANK explains the differences.

Why is COUNTIF returning 0 or the wrong number?#

COUNTIF returns 0 when nothing matches the criteria exactly, and the cause is usually a small detail. Check these in order:

1. Missing quotes. =COUNTIF(D2:D9,Open) gives #NAME?. Write "Open".
2. Extra spaces in the data. "Open " with a trailing space doesn't match "Open". Clean the column with TRIM first.
3. Numbers stored as text. If the Hours column was imported as text, ">=8" won't count it. Text usually sits on the left of the cell. Convert it with VALUE.
4. Wrong operator. "=>8" and "!=Low" don't work. Use ">=8" and "<>Low".
5. Wrong range. Make sure the range is the column you mean, and that it covers every row. A range that stops at row 8 quietly drops the last ticket.

How to count with more than one condition#

COUNTIF checks one condition only. For rows that must match two things at once, such as High priority and Open, use COUNTIFS:

=COUNTIFS(C2:C9,"High",D2:D9,"Open")

The result is 2 (T-1001 and T-1005). The guide to COUNTIFS and SUMIFS with multiple conditions covers this in detail.

For OR logic, such as High or Medium, add two COUNTIFs:

=COUNTIF(C2:C9,"High")+COUNTIF(C2:C9,"Medium")

That gives 3 + 3 = 6. Once you can count, the same criteria work for totals and averages too. Learn how SUMIF adds up matching numbers and how AVERAGEIF finds a conditional average. The COUNTIF function guide is a quick reference for the syntax.

Practise it#

Reading about criteria is easy. Typing them under time pressure is what a job test checks. Practise COUNTIF in a live spreadsheet: you'll count open tickets, use ">=8" and "<>Low", and fill a cell-based formula down a summary table. Or try the exercise on this page first.