COUNT vs COUNTIF in Excel

The short answer to COUNT vs COUNTIF: COUNT tells you how many cells contain numbers, and COUNTIF tells you how many cells meet a condition you set. COUNT has no condition at all, while COUNTIF works with text, numbers and dates. This guide compares them side by side on one table, for anyone choosing between them in a report or a job test.

COUNT vs COUNTIF at a glance#

COUNT answers "how many numbers are there?". COUNTIF answers "how many cells match this?".

Both functions belong to a family. COUNTA counts every filled cell, COUNTBLANK counts empty cells, and COUNTIFS counts with several conditions.

How COUNT works#

COUNT looks at each cell and counts the ones that hold a number. Dates and times count too, because Excel stores them as numbers. Text, empty cells, errors and TRUE or FALSE in a range are ignored.

=COUNT(C2:C9)

You can give COUNT several ranges at once, such as =COUNT(C2:C9,F2:F9), and it adds the counts together. The COUNT function guide has the full rules, including how it treats values typed straight into the formula.

COUNT is a quick data check. If a column should be all numbers and COUNT is lower than the number of rows, something is missing or stored as text.

How COUNTIF works#

COUNTIF checks each cell in one range against a condition, called the criteria, and counts the matches. The criteria can be text, a number, a comparison or a cell reference:

=COUNTIF(D2:D9,"Pass")
=COUNTIF(C2:C9,">=70")

Text and operators go in double quotes. Numbers and cell references don't need them. COUNTIF ignores capital letters, and it accepts wildcards: * matches any number of characters and ? matches one. The COUNTIF function guide lists every criteria type.

The same data counted both ways#

Here is a small table of test results. Chen was absent, so his score is the word "Absent", and Eli's score is blank:

Task 1: how many candidates have a score?#

COUNT gives the answer directly:

=COUNT(C2:C9)

The result is 6. The word "Absent" and the blank cell are not numbers, so COUNT skips them. COUNTA would return 7, because it counts "Absent" as a filled cell. The COUNT and COUNTA lesson practises the difference.

Task 2: how many scored 70 or more?#

COUNT can't do this, because it has no condition. COUNTIF can:

=COUNTIF(C2:C9,">=70")

The result is 4 (82, 91, 73 and 88). COUNTIF only compares numbers with ">=70", so "Absent" and the blank are left out.

Task 3: how many passed?#

The Result column is text, so COUNT returns 0:

=COUNT(D2:D9)

COUNTIF counts the word:

=COUNTIF(D2:D9,"Pass")

The result is 4 (Ana, Dina, Fatima and Hana).

Task 4: how many cells are filled, or have any text?#

COUNTIF can mimic other counting functions. =COUNTIF(C2:C9,"<>") counts non-empty cells: 7, the same as COUNTA. =COUNTIF(C2:C9,"*") counts only text cells: 1, the word "Absent". For more on these two patterns, see counting cells with text and COUNTIF not blank.

When to use COUNT#

Use COUNT when you need the number of numeric values and there is no condition. It fits well when:

• You check that data is complete. If 8 people took the test and =COUNT(C2:C9) returns 6, two scores are missing or not numbers.
• You find numbers stored as text. Compare COUNT with COUNTA on a column of amounts. If COUNTA is higher, some "numbers" are text.
• You count dates. =COUNT(A2:A100) on a date column counts real dates and skips text like "TBC".
• You count across several ranges at once, such as =COUNT(C2:C9,F2:F9).
• You need an average by hand. SUM divided by COUNT gives the same result as AVERAGE, which is a common test question.

When to use COUNTIF#

Use COUNTIF whenever the question has a condition: "how many are...?", "how many over...?", "how many not...?". It is the better choice when:

• You count a word or category, such as Pass, HR or Open.
• You count numbers in a band, such as scores of 70 or more: ">=70".
• You exclude something, such as everyone except HR: =COUNTIF(B2:B9,"<>HR") returns 5.
• You count partial text with wildcards, such as every code starting with "INV": "INV*".
• You count blanks or non-blanks in a specific way, with "" or "<>".
• You check duplicates, for example =COUNTIF($A$2:$A$9,A2)>1; see how to count duplicates.

When the question has two conditions, such as HR candidates who scored 60 or more, use COUNTIFS: =COUNTIFS(B2:B9,"HR",C2:C9,">=60") returns 2 (Ben and Hana). The comparison of COUNTIF vs COUNTIFS explains when to switch. And if you need to add up values rather than count them, see SUMIF vs COUNTIF.

Common mistakes with COUNT and COUNTIF#

These are the slips that most often give wrong counts:

1. Using COUNT on text. =COUNT(D2:D9) on Pass and Fail returns 0. Use COUNTIF for a word, or COUNTA for any filled cell.
2. Trying to give COUNT a condition. =COUNT(D2:D9,"Pass") doesn't count passes. COUNT treats "Pass" as another value, and because it isn't a number, the result is still the count of numbers in D2:D9: 0.
3. Numbers stored as text. Imported scores that are text are skipped by COUNT and by numeric criteria like ">=70". Convert them with VALUE or Data > Text to Columns.
4. Leaving the operator outside the quotes. Write ">=70", not >=70. And to use a cell, join it with &: ">="&G1.
5. Expecting COUNT to count blanks. It never does. Use COUNTBLANK or =COUNTIF(C2:C9,"").
6. Giving COUNTIF two ranges. =COUNTIF(B2:B9,"HR",C2:C9,">=60") won't go in: Excel shows a "too many arguments" message. Use COUNTIFS.

Our recommendation#

Learn both, because they answer different questions and job tests ask for both. Use COUNT for a quick check of how many numeric values a range holds, and to spot missing or text numbers. Use COUNTIF whenever the question includes a condition, which is most real counting questions in reports.

In short: no condition and numbers only, COUNT; any condition, COUNTIF; two or more conditions, COUNTIFS.

Practise it#

Try the exercise on this page: it counts the scores of 70 or more, which COUNT can't do. Then practise COUNT, COUNTA and COUNTBLANK in a live spreadsheet, where each formula is checked instantly, and move on to the COUNTIF lesson to count words, number bands and cell-based criteria. To see where your counting skills stand overall, take the Excel level test.