How to Use COUNTIF in Excel

You need to know how many orders are marked Shipped, how many scores are above 80, or how many names contain a certain word. COUNTIF counts the cells in a range that meet one condition.

Type the basic formula

Click an empty cell and enter:

=COUNTIF(A2:A50, "Shipped")

• range: the cells to look at, here A2:A50.
• criteria: the condition a cell must meet. Text goes in quotes.

If 12 cells in A2:A50 contain Shipped, the result is 12. Matching is not case-sensitive, so shipped and SHIPPED are counted too.

Point the criteria at a cell

Reference a cell so you can change the condition without editing the formula:

=COUNTIF(A2:A50, D1)

Whatever is typed in D1 becomes the condition. Type Pending there and the count updates.

Count numbers above or below a value

Put the comparison operator and the number together inside quotes:

=COUNTIF(B2:B50, ">100")

This counts the cells in B2:B50 greater than 100. Other operators work the same way: "<100", ">=100", and "<>100" for not equal. To compare with a cell value, join the operator to the reference with an ampersand: =COUNTIF(B2:B50, ">"&D1).

Count cells that contain part of a word

Use wildcards. * stands for any number of characters and ? stands for exactly one:

=COUNTIF(A2:A50, "*west*")

This counts every cell containing "west" anywhere in the text, such as Westfield or Southwest. "J???" counts entries that are exactly four characters long and start with J. To count a literal * or ?, put a tilde in front: "~*".

Count blank or filled cells

=COUNTIF(A2:A50, "") counts empty cells. =COUNTIF(A2:A50, "<>") counts cells that contain anything at all.

To count with more than one condition. Use COUNTIFS, which takes range and criteria in pairs: =COUNTIFS(A2:A50, "Shipped", B2:B50, ">100") counts rows that are both Shipped and over 100. Every condition must be true for a row to be counted, and all ranges must be the same size.