What is COUNTIF?

COUNTIF counts cells that meet a specific condition. It's like COUNT, but with a filter.

Basic Syntax

=COUNTIF(range, criteria)

range: Cells to check
criteria: What to look for

Simple Examples

Count Specific Value

=COUNTIF(A1:A10, "Apple")
Counts how many cells contain "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 cells with exactly 3 characters (? = one char)

Real-World Examples

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 lets you count with multiple conditions (AND logic)

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

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"

=COUNTIF(A1:A10, "<>0")
Counts all non-zero values

Date Examples

Count dates in current year:
=COUNTIFS(A1:A100, ">="&DATE(2024,1,1), A1:A100, "<"&DATE(2025,1,1))

Count dates in last 30 days:
=COUNTIF(A1:A100, ">="&TODAY()-30)

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

Common Errors and Fixes

#VALUE! Error

Cause: Invalid criteria format

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: Use TRIM(range)
- Text vs numbers: "50" ≠ 50
- Case sensitivity: COUNTIF is NOT case-sensitive
  Use SUMPRODUCT for case-sensitive:
  =SUMPRODUCT((A1:A10="Apple")*1)

Criteria with Cell References

WRONG: =COUNTIF(A1:A10, ">B1")
This looks for literal text ">B1"

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

COUNTIF vs Alternatives

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

Count Unique Values

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

Older Excel:
=SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100))

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

• 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

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

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

Best Practices

• 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)