COUNTIF

COUNTIF counts the cells in a range that meet one condition: =COUNTIF(range, criteria). Use it to count how many orders came from a region, how many scores pass, or how many times a name appears.

When to use it

• You need to know how many rows match a text, number or date test.
• You want to find duplicates by counting how often each value appears.
• You count blanks or non-blanks with "" and "<>" criteria.
• You need more than one condition: use COUNTIFS.

Arguments explained

• range. The cells to examine. It can be a column, a row or a block, but only a single range is allowed.
• criteria. What to count. Use a value for an exact match, a quoted expression such as ">50" or "<>" (not blank), or wildcards such as "A*" for text starting with A. To use a cell reference with an operator, join them: ">"&D1.
• Notes. Matching of text is not case sensitive. Numbers stored as text and real numbers can behave differently, so keep data types consistent.

Worked examples

Common errors and how to fix them

• Count too low for text numbers. Criteria match numbers and text numbers differently in some cases. Make sure both sides are the same type.
• Long text criteria fail. COUNTIF cannot match text strings longer than 255 characters, and returns #VALUE!.
• Operators outside quotes. Write ">=1000" in quotes. For a cell reference use ">="&E1.
• Whole row counted wrongly. Counting across a two-dimensional range counts each cell, not each row.

Related functions

• COUNTIFS
• SUMIF
• COUNTA
• COUNTBLANK
• UNIQUE

Frequently asked questions

How do I count cells that are not empty?

Use =COUNTIF(A2:A100,"<>") or COUNTA.

How do I count duplicates?

Add a helper column =COUNTIF($A$2:$A$100,A2) and filter for values greater than 1.

Can COUNTIF count with OR?

Add two COUNTIFs, or use SUM(COUNTIF(A:A,{"East","West"})).

Is COUNTIF case sensitive?

No.