COUNTIF in Excel: How to Use the COUNTIF Function

COUNTIF in Excel counts the number of cells in a range that meet a single condition, such as counting how many times a name appears or how many sales are above a certain amount. You enter a range and a criteria, and Excel returns the count of matching cells. The syntax is =COUNTIF(range, criteria), and it works with numbers, text, wildcards, and even other cell references.

Formula

The COUNTIF function counts cells in a range that match a specified condition. The formula is:

=COUNTIF(range, criteria)

• range – the group of cells to examine (e.g., A2:A100).
• criteria – the condition that determines which cells to count. It can be a number, text, expression, or cell reference.

For example, =COUNTIF(B2:B10, "Yes") counts how many cells in B2:B10 contain exactly the word “Yesâ€�. The criteria is not case-sensitive, so “yesâ€� and “YESâ€� are treated the same.

Worked Example

Suppose you have a list of 10 products in column A, and you want to count how many are “Shirt�. The range is A2:A11, and the criteria is “Shirt�. The formula is:

=COUNTIF(A2:A11, "Shirt")

If the list contains: Shirt, Pants, Shirt, Hat, Shirt, Shoes, Shirt, Shirt, Pants, Shirt – that’s 6 shirts. Excel counts each cell that matches exactly, so the result is 6.

Now change the criteria to count cells that are not empty: =COUNTIF(A2:A11, "<>") returns 10 because all cells have text.

How COUNTIF Works Under the Hood

COUNTIF evaluates each cell in the range against the criteria. The criteria can be a number, text, a comparison operator (like > or <), or a wildcard (* for any sequence, ? for a single character). The function ignores blank cells and does not count cells that contain errors unless the criteria specifically matches them.

Key behaviors:

• Text criteria must be enclosed in double quotes: "Apple".
• Numeric criteria can be typed directly: 10 or ">10".
• To reference another cell, use the cell reference without quotes: ">"&D1.
• Wildcards work only with text: "A*" matches any text starting with A.

How to Choose the Right Criteria

Choosing the right criteria is critical. For exact matches, type the value or text. For partial matches, use wildcards. For numeric comparisons, use operators like >, <, >=, <=, or <> (not equal).

• Exact text: "Red" – counts cells that equal “Redâ€�.
• Contains text: "*Red*" – counts cells that contain “Redâ€� anywhere.
• Greater than a number: ">100" – counts cells with values over 100.
• Not equal to: "<>Pending" – counts cells that are not “Pendingâ€�.

For dates, use a date serial number or a cell reference: ">"&DATE(2024,1,1) counts dates after Jan 1, 2024.

Common Mistakes and How to Avoid Them

Even experienced users trip up on COUNTIF. Here are the most frequent errors:

• Forgetting quotes around text criteria – COUNTIF(A1:A10, Shirt) returns a #NAME? error. Always use double quotes.
• Using a cell reference inside quotes – COUNTIF(A1:A10, ">"&B1) is correct; COUNTIF(A1:A10, ">B1") treats “B1â€� as text and returns 0.
• Mixing numbers and text – If a cell contains the number 5 and you use "5" as criteria, it may not count because Excel distinguishes between text and numbers. Use the number without quotes.
• Case sensitivity – COUNTIF is not case-sensitive. If you need case-sensitive counting, use SUMPRODUCT with EXACT.
• Counting blank cells – To count truly empty cells, use "="; to count cells that are not blank, use "<>". But "<>" also counts cells with formulas that return an empty string, which may be unexpected.

COUNTIF vs. COUNTIFS vs. SUMPRODUCT

COUNTIF handles a single condition. When you need multiple conditions, use COUNTIFS, which accepts multiple range/criteria pairs. For example, =COUNTIFS(A:A,"Shirt",B:B,"Large") counts rows where column A is “Shirtâ€� and column B is “Largeâ€�.

SUMPRODUCT is a more flexible alternative that can handle arrays and even case-sensitive matches. For instance, to count cells that are exactly “Shirtâ€� (case-sensitive), you can use =SUMPRODUCT(--EXACT(A1:A10,"Shirt")). It’s heavier but useful for complex logic.

When to use which:

• Single condition → COUNTIF
• Multiple conditions (all must be true) → COUNTIFS
• Case-sensitive or complex array logic → SUMPRODUCT

Practical Examples You Can Use Today

Here are a few real-world scenarios where COUNTIF shines:

• Count duplicate entries – =COUNTIF(A:A, A2) in a helper column shows how many times each value appears.
• Count orders above a threshold – =COUNTIF(Sales, ">1000") tells you how many orders exceeded $1,000.
• Count specific text in a mixed list – =COUNTIF(Status, "Completed") counts completed tasks.
• Count blank cells – =COUNTIF(A:A, "=") returns the number of empty cells in column A.
• Count cells that contain a word – =COUNTIF(A:A, "*apple*") counts cells that contain “appleâ€� anywhere.

These patterns work in Excel 2010, 2013, 2016, 2019, 365, and Google Sheets (where the syntax is identical).

Frequently Asked Questions

Can I use COUNTIF with a cell reference as criteria?

Yes, but you must concatenate operators with the reference. For example, to count cells greater than the value in D1, use =COUNTIF(A:A, ">"&D1). If you put D1 inside quotes, Excel treats it as literal text and the count will be 0.

How do I count cells that contain a specific text, not exactly equal?

Use wildcards: =COUNTIF(A:A, "*text*") counts cells that contain “textâ€� anywhere. The asterisk matches any number of characters. For a single character, use a question mark, like "A?" matches “ABâ€� but not “Aâ€�.

Why does COUNTIF return 0 when I know there are matches?

Common reasons: the criteria is typed as text when cells contain numbers (or vice versa), there are leading/trailing spaces, or the criteria uses a cell reference incorrectly. Check for spaces with =TRIM() and ensure numbers are not stored as text (look for a green triangle in the cell).

Is COUNTIF case-sensitive?

No, COUNTIF is not case-sensitive. “appleâ€� and “APPLEâ€� are treated the same. If you need case-sensitive counting, use =SUMPRODUCT(--EXACT(range,"text")).

Can I count cells based on color?

No, COUNTIF cannot count by cell color. You would need to use VBA or a helper column that stores the color value. Alternatively, use the SUBTOTAL function with a filter, but that still doesn’t count by color directly.

What is the difference between COUNTIF and COUNT?

COUNT counts only numeric cells, while COUNTIF counts cells that meet a condition, regardless of data type. For example, =COUNT(A:A) counts numbers only; =COUNTIF(A:A,"*") counts all non-empty text cells.

Related

• Crop Genome Sequencing: Drought, Pests, Yield
• LLM Eval Scores: What Acc and Acc Norm Measure
• Make Pinterest Board Private: Desktop & Mobile

in d | Distance | km | 10 | 0
in t | Finish time | min | 50 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2