Learn how to use Excel’s COUNTIF function effectively. COUNTIF counts cells that meet your specified criteria, perfect for data analysis and reporting.

Syntax

=COUNTIF(range, criteria)

• range: The range of cells to evaluate
• criteria: The condition to test (e.g., “>10”, “Sold”, “2024”, “>=1/1/2024”)

Examples

1. Count numbers greater than a value:
   =COUNTIF(A2:A100, “>1000”)
   Result: Counts how many cells contain numbers > 1000

2. Count specific text:
   =COUNTIF(B2:B50, “Completed”)
   Result: Counts cells containing exactly “Completed”

3. Count dates before a specific date:
   =COUNTIF(C2:C100, “<1/1/2024”)
   Result: Counts dates before January 1, 2024

4. Count using cell reference:
   =COUNTIF(A2:A100, A1)
   Result: Counts cells matching value in A1

5. Count with wildcards:
   =COUNTIF(B2:B50, “Apple*”)
   Result: Counts cells starting with “Apple”

Advanced Tips

• Use wildcards for partial matches:

  • “*” matches any number of characters
  • “?” matches any single character
    Example: =COUNTIF(A2:A100, “2024*”) counts items starting with “2024”

• Comparison operators:

  • > (greater than)
  • < (less than)
  • >= (greater than or equal)
  • <= (less than or equal)
  • <> (not equal)

• Date comparisons:

  • Use proper date format
  • Can compare using operators
    Example: =COUNTIF(A2:A100, “>=1/1/2024”)

Common Errors

• #VALUE!: Invalid criteria format
• #NAME?: Missing quotes around text criteria
• Case-sensitive: “SOLD” ≠ “sold”
• Zero results: Check data format matches criteria