Statistical

COUNTIF Function in Excel

Counts the number of cells that meet a criterion.

Syntax

  • =COUNTIF(range, criteria)

Arguments

  • range (required): The range of cells to count
  • criteria (required): The condition to match

Examples

  • =COUNTIF(A:A, "Apple") - Count cells containing 'Apple' - Result: 15
  • =COUNTIF(B:B, ">100") - Count values greater than 100 - Result: 8
  • =COUNTIF(C:C, "<>"&"") - Count non-empty cells - Result: 50

Overview

  • COUNTIF answers “how many?” for one rule — open tickets, orders over $1k, cells containing a word. Pair it with SUMIFS on the same dashboard for count + dollars.

High-value COUNTIF patterns

  • Status Open: =COUNTIF(Status,"Open").
  • Partial match: =COUNTIF(Notes,"*urgent*").
  • Above average flag helper: =COUNTIF(Score,">"&AVERAGE(Score)).

COUNTIF vs COUNTA vs COUNTIFS

  • COUNTA: any non-empty cell.
  • COUNTIF: cells matching a rule.
  • COUNTIFS: two or more AND conditions.

Data quality workflows

  • Duplicate flag: =COUNTIF($A:$A,A2)>1 with conditional formatting.
  • [Remove duplicates](/blog/remove-duplicates-in-excel/) before deleting rows.

Quick answer: countif excel

  • COUNTIF counts cells in a range that match one condition. Use quotes around criteria; wildcards * and ? work inside the criteria string.
  • Closed
  • For two or more conditions, use COUNTIFS.

COUNTIF best practices

  • Use COUNTIF to count records that meet one condition, such as open tasks or values above a threshold.
  • Use COUNTIFS when the count depends on several filters at once.
  • Put text criteria in quotes and use operators like ">100" when counting numeric thresholds.

COUNTIF vs COUNTA decision guide

  • Use COUNTIF when you need a count that meets a rule, such as status = Open or amount > 1000.
  • Use COUNTA when you only need non-empty cells regardless of value.
  • Use COUNTIFS when two or more filters must all be true.

Wildcard and audit patterns

  • Partial match: =COUNTIF(A:A,"*north*").
  • Count blanks: =COUNTIF(A:A,"").
  • Flag duplicates for review: helper =COUNTIF($A:$A,A2)>1 with conditional formatting.

Related guides

  • Compare [COUNTIF vs COUNTIFS](/compare/countif-vs-countifs/).
  • Use with [remove duplicates](/blog/remove-duplicates-in-excel/) workflows before deleting rows.

People also ask

  • How do I count cells with specific text? — =COUNTIF(range,"exact text") or wildcards *text*.
  • Does COUNTIF count blank cells? — =COUNTIF(range,"").

Common errors

  • Returns 0 if no matches
  • #VALUE! with invalid criteria

Use cases

  • Data analysis
  • Inventory counts
  • Survey responses

Frequently asked questions

  • Is COUNTIF case-sensitive? No. COUNTIF ignores case. Check spaces and wildcards instead.
  • How do I count cells greater than a value? =COUNTIF(range,">100") or ">"&cell for a dynamic threshold.
  • COUNTIF vs COUNTIFS? Use COUNTIFS when two or more conditions must all be true.
  • What is COUNTIF in Excel and how does it work? COUNTIF counts cells that meet a specific condition. The syntax is =COUNTIF(range, criteria). For example, =COUNTIF(A:A, "Apple") counts how many cells in column A contain "Apple". =COUNTIF(B:B, ">100") counts cells greater than 100. It's essential for data analysis and creating summary statistics.
  • What is the difference between COUNTIF and COUNTIFS? COUNTIF handles one condition, COUNTIFS handles multiple conditions (AND logic). COUNTIF: =COUNTIF(range, criteria). COUNTIFS: =COUNTIFS(range1, criteria1, range2, criteria2, ...). Example: =COUNTIFS(A:A, "Sales", B:B, ">1000") counts rows where column A is "Sales" AND column B is greater than 1000.
  • How do I count cells that are not blank using COUNTIF? To count non-blank cells: =COUNTIF(A:A, "<>") or =COUNTIF(A:A, "<>"&""). To count blank cells: =COUNTIF(A:A, "") or use =COUNTBLANK(A:A). To count cells with any text: =COUNTIF(A:A, "*"). To count cells with numbers: use =COUNT(A:A) which only counts numeric values.
  • How do I use wildcards in COUNTIF? COUNTIF supports wildcards: * matches any characters, ? matches single character. Examples: =COUNTIF(A:A, "*apple*") counts cells containing "apple"; =COUNTIF(A:A, "???") counts 3-character entries; =COUNTIF(A:A, "A*") counts entries starting with A. To find literal * or ?, use ~* or ~?.
  • Why is COUNTIF returning 0 when there are matching values? COUNTIF returns 0 when: 1) Criteria has extra spaces - check with LEN(); 2) Numbers stored as text (or vice versa); 3) Criteria syntax is wrong - use quotes for text and operators: ">100" not >100; 4) Range doesn't include the cells you expect; 5) Hidden characters in data - use CLEAN() and TRIM(). Verify data types match.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.