Excel COUNTIF Function

The Excel COUNTIF function counts the cells in a range that meet one condition, using =COUNTIF(range, criteria). The criteria can be a value, a cell reference, a comparison such as ">50", or a wildcard pattern such as "*apple*", and matching is not case-sensitive. For two or more conditions, use COUNTIFS.

What Is the COUNTIF Syntax?

COUNTIF takes two arguments, =COUNTIF(range, criteria): the cells to check and the condition a cell must meet to be counted. Put text and comparisons in double quotes ("Apple", ">50"); numbers and cell references can go in without quotes.

=COUNTIF(range, criteria)

range: Cells to check
criteria: What to look for

How Do You Use COUNTIF With Text, Operators and Wildcards?

Type the value, comparison or pattern as the criteria: "Apple" counts cells equal to Apple in any letter case, ">50" counts numbers above 50, and "*apple*" counts text containing "apple" anywhere. To compare against a cell, join the operator to the reference with &, as in "<"&B1.

Count Specific Value

=COUNTIF(A1:A10, "Apple")
Counts cells equal to "Apple" (also "apple" or "APPLE")

=COUNTIF(A1:A10, 100)
Counts how many cells equal 100

=COUNTIF(A1:A10, B1)
Counts cells that match value in B1

Count with Comparison Operators

=COUNTIF(A1:A10, ">50")
Counts values greater than 50

=COUNTIF(A1:A10, ">=100")
Counts values 100 or more

=COUNTIF(A1:A10, "<"&B1)
Counts values less than B1 (note the &)

Count Text Patterns

=COUNTIF(A1:A10, "A*")
Counts cells starting with "A" (* = wildcard)

=COUNTIF(A1:A10, "*apple*")
Counts cells containing "apple" anywhere

=COUNTIF(A1:A10, "???")
Counts text cells with exactly 3 characters (? = one char)

What Are Some Practical COUNTIF Examples?

Three everyday COUNTIF jobs are counting one status, counting values above a target and counting non-empty cells: =COUNTIF(A1:A10, "Complete"), =COUNTIF(B2:B100, ">"&C1) with the target in C1, and =COUNTIF(A1:A10, "<>").

Example 1: Count Status

Task List:
A1: Complete
A2: Pending
A3: Complete
A4: In Progress
A5: Complete

=COUNTIF(A1:A10, "Complete")
Result: 3

Example 2: Count Sales Above Target

Sales in B2:B100
Target in C1 (e.g., 50000)

=COUNTIF(B2:B100, ">"&C1)
Counts how many salespeople exceeded target

Example 3: Count Non-Blank Cells

=COUNTIF(A1:A10, "<>")
Counts all non-empty cells

Alternative:
=COUNTA(A1:A10)

COUNTIFS - Multiple Criteria

COUNTIFS counts the rows where every condition is true (AND logic), with one range/criteria pair per condition. All ranges must be the same size.

Syntax

=COUNTIFS(range1, criteria1, range2, criteria2, ...)

Up to 127 range/criteria pairs

Examples

Count sales in California over $50K:
=COUNTIFS(B2:B100, ">50000", C2:C100, "California")

Count products with "Widget" in name and price < $100:
=COUNTIFS(A2:A100, "*Widget*", B2:B100, "<100")

Count employees aged 25-40:
=COUNTIFS(D2:D100, ">=25", D2:D100, "<=40")

Common Use Cases

Beyond simple counts, COUNTIF flags duplicates, counts values between two limits, handles OR conditions when you add two COUNTIFs together, and excludes a value with "<>".

1. Count Duplicates

Count how many times each value appears:
=COUNTIF($A$1:$A$100, A1)
Drag down to see count for each row

Find only duplicates (count > 1):
=COUNTIF($A$1:$A$100, A1) > 1

2. Count Between Two Values

Count values between 50 and 100:
=COUNTIFS(A1:A10, ">=50", A1:A10, "<=100")

Or:
=COUNTIF(A1:A10, ">=50") - COUNTIF(A1:A10, ">100")

3. Count with OR Logic

Count "Complete" OR "Done":
=COUNTIF(A1:A10, "Complete") + COUNTIF(A1:A10, "Done")

Count values <10 OR >90:
=COUNTIF(A1:A10, "<10") + COUNTIF(A1:A10, ">90")

4. Count Not Equal To

=COUNTIF(A1:A10, "<>Pending")
Counts everything except "Pending", including blank cells

=COUNTIF(A1:A10, "<>0")
Counts every cell that isn't 0, including blank and text cells

Date Examples

COUNTIF treats dates as numbers, so join a comparison operator to DATE() or TODAY() with &. Use COUNTIFS with two conditions on the same column to count dates between a start and an end date.

Count dates in the current year:
=COUNTIFS(A1:A100, ">="&DATE(YEAR(TODAY()),1,1), A1:A100, "<"&DATE(YEAR(TODAY())+1,1,1))

Count dates in the last 30 days (excluding future dates):
=COUNTIFS(A1:A100, ">="&TODAY()-30, A1:A100, "<="&TODAY())

Count future dates:
=COUNTIF(A1:A100, ">"&TODAY())

Common Errors and Fixes

Most COUNTIF problems come from criteria without quotes, a cell reference typed inside the quotes, or data that looks identical but isn't, such as trailing spaces.

Criteria Without Quotes

Cause: an operator outside quotes. Excel rejects the formula
with "There's a problem with this formula".

WRONG: =COUNTIF(A1:A10, >50)
RIGHT: =COUNTIF(A1:A10, ">50")

WRONG: =COUNTIF(A1:A10, >=B1)
RIGHT: =COUNTIF(A1:A10, ">="&B1)

Not Counting Correctly

Check for:
- Extra spaces: clean the data with TRIM in a helper column
- Case sensitivity: COUNTIF is NOT case-sensitive
  Use SUMPRODUCT with EXACT for a case-sensitive count:
  =SUMPRODUCT(--EXACT(A1:A10, "Apple"))

Criteria with Cell References

WRONG: =COUNTIF(A1:A10, ">B1")
This compares against the text "B1", not the value in cell B1

RIGHT: =COUNTIF(A1:A10, ">"&B1)
Concatenate operator with cell reference

COUNTIF vs Alternatives

Use COUNTIF when you need to count cells that meet one condition. COUNT, COUNTA and COUNTBLANK need no condition, COUNTIFS handles several AND conditions, and SUMPRODUCT covers OR logic and case-sensitive matching.

Function	Use When
COUNT	Count numbers only
COUNTA	Count non-empty cells
COUNTBLANK	Count empty cells
COUNTIF	Count with one condition
COUNTIFS	Count with multiple conditions (AND)
SUMPRODUCT	Complex conditions, OR logic, case-sensitive

Advanced Techniques

COUNTIF also drives distinct counts, percentages of a total, and conditional formatting rules that highlight duplicates.

Count Unique Values

Excel 365 (dynamic arrays):
=COUNTA(UNIQUE(A1:A100))

Older Excel (range with no blank cells):
=SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100))

For the full set of approaches (including pivot-table distinct counts), see count unique values in Excel. For summing instead of counting, the same criteria syntax works in SUMIF.

Percentage Calculations

Percentage of "Complete" tasks:
=COUNTIF(A1:A10, "Complete") / COUNTA(A1:A10)

Format as percentage or:
=TEXT(COUNTIF(A1:A10, "Complete")/COUNTA(A1:A10), "0%")

Conditional Formatting with COUNTIF

Highlight duplicates:
1. Select range A1:A10
2. Conditional Formatting → New Rule
3. Use formula: =COUNTIF($A$1:$A$10, A1)>1
4. Choose format

Performance Tips

A single COUNTIF calculates instantly; slowdowns come from thousands of copies, such as a duplicate check filled down every row of a large range, where each formula scans the whole range.

• Use absolute references ($) for range: =COUNTIF($A$1:$A$100, ...)
• Avoid entire columns: =COUNTIF(A:A, ...) is slow
• Use COUNTIFS instead of nested IFs
• Consider pivot tables for complex counting

Quick Reference

Pick the pattern that matches your condition and replace range with your cells; the same criteria strings work in COUNTIFS, SUMIF and SUMIFS.

Exact match:        =COUNTIF(range, "value")
Greater than:       =COUNTIF(range, ">50")
Cell reference:     =COUNTIF(range, ">"&B1)
Contains text:      =COUNTIF(range, "*apple*")
Starts with:        =COUNTIF(range, "A*")
Not equal:          =COUNTIF(range, "<>value")
Multiple criteria:  =COUNTIFS(range1, crit1, range2, crit2)

Common Patterns

Some counts don't need a condition at all: COUNTBLANK, COUNT and COUNTA cover blanks, numbers and non-empty cells, while COUNTIF handles text, specific error values and TRUE/FALSE.

Count non-blanks:   =COUNTIF(A1:A10, "<>")
Count blanks:       =COUNTBLANK(A1:A10)
Count numbers:      =COUNT(A1:A10)
Count text:         =COUNTIF(A1:A10, "*")
Count #N/A errors:  =COUNTIF(A1:A10, "#N/A")
Count all errors:   =SUMPRODUCT(--ISERROR(A1:A10))
Count TRUE:         =COUNTIF(A1:A10, TRUE)
Count FALSE:        =COUNTIF(A1:A10, FALSE)

Best Practices

Five habits prevent most wrong counts: quote the criteria, join operators to cell references with &, lock ranges with $, test on a small sample, and switch to COUNTIFS for multiple conditions.

• Always use quotes around criteria: ">50" not >50
• Use & to concatenate cell references with operators
• Lock ranges with $ when copying formulas
• Test with small data first to verify criteria
• Use COUNTIFS for multiple conditions (cleaner than multiple COUNTIF)