Sign Up

COUNTIF Function

The COUNTIF function in Excel returns the counted number of cells within a range of cells which meet a single criterion specified by the user. You can use COUNTIF to count the number of cells which meet a condition based on text, numerical comparisons, wildcard comparisons, dates, and more.

COUNTIF Function Syntax

The COUNTIF Function has two required arguments (values separated by commas) and is written as follows:

=COUNTIF(range, criteria)

range

The range argument defines which cells are being tested against the user's criteria. Excel will be counting the number of cells within this range that meet the criteria.

criteria

The criteria argument is the user's criteria definition that determines which cells to count. Excel counts the number of cells in the range which meet this criteria.

What Types of Arguments can COUNTIF Take?

As with most Excel functions, the COUNTIF function has certain inputs it can comprehend and calculate. Here's a quick sample of types of values that COUNTIF can process:

The COUNTIF function can use Excel's logical operators to determine whether a cell meets the user's criteria and should be counted. Here's a quick review of the logical operators in Excel.

Operator
Meaning
>
Greater than
<
Less Than
>=
Greater than or equal to
<=
Less than or equal to
=
Equal to
<>
Not equal to

The COUNTIF function can use Excel's wildcard matching to evaluate matches to its criteria. Here's a quick review of wildcard matching in Excel.

Wildcard
Meaning
*
Wildcard of undefined length
?
Single character wildcard

The wildcard operators in Excel are a question mark (?) and an asterisk (*). If you need to use a literal question mark or asterisk in your function, place a tilde (~) before the symbol (~?) (~*).

Examples Using the COUNTIF Function in Excel

Take a look at the spreadsheet example below. Say your boss asks you to find the number of categories, by year, which exceed $30,000. This is an easy job with the COUNTIF function, even if there were many more rows and columns.

To accomplish this task, we need the count of cells in each column which are greater than 30,000. For our COUNTIF formulas, our ranges will be E5:E9, F5:F9, and G5:G9, respectively. Our criteria will be "greater than 30,000."

Double-click into cell G11 (column G, row 11), and type the following formula:

=COUNTIF(G5:G9, ">30000")

Double-click into cell F11 and type the following formula:

=COUNTIF(F5:F9, ">30000")

Double-click into cell E11 and type the following formula:

=COUNTIF(E5:E9, ">30000")

You can now see that for 2018 and 2019, Apple only had two categories above $30,000. But in 2020, Apple had three categories above $30,000!

You'll also notice that we enclosed our criteria argument in quotation marks. Using quotations around defined criteria is necessary when using logical operators, text, and wildcards in Excel. The only time quotations are not necessary is when your criteria are plainly numerical and lack a logical operator or wildcard. For example, if you wanted to know whether 2020 was included in the years, you could type =COUNTIF(4:4, 2020) (without quotes!) which would return a value of 1, because 2020 shows up exactly once in row 4.

Using the COUNTIF Function With Text

We can also use COUNTIF to count the number of cells which contain text that meet a certain criteria. Say that we want to know the number of categories that begin with the letter "i" (like iPhone and iPad). This isn't a terribly difficult problem as is, but if there were hundreds of categories we would have a harder time.

To find the number of category names that begin with "i" double-click into cell C11 and type the following formula:

=COUNTIF(C5:C9, "i*")

This formula should return a value of 2. Our range is the cells that include the category names (C5:C9). Our criteria is enclosed in quotes because it includes text. We also use a wildcard character - recall that the asterisk (*) is a wildcard character of unknown length. The criteria "i*" essentially means "any text that begins with i followed by any characters of unknown length."

Now say you want to know how many category names do not begin with the letter "i." You could use the same logic, but with the not equal to operator (<>). Double-click into cell C12 and type the following formula:

=COUNTIF(C5:C9, "<>i*")

This formula should return a value of 3, because our criteria is now asking for category names which do not begin with the letter "i."

Using Cell References With the COUNTIF Function

Now suppose your manager looks at the spreadsheet in the example above and has a new question for you. It seems that the Services category made a lot of money in 2020. Your manager wants to know how many times a category, in any of the three years, made more money than Services made in 2020.

To do this we can write a COUNTIF formula which references the cell that shows how much Services made in 2020. That cell is E9.

Our range is all categories for all years (E5:G9) and our criteria is greater than the value in E9.

Recall that when we use greater than (a logical operator) in our criteria, we need to enclose it in quotes. But a cell reference can't go inside quotes, because then Excel will just think that it is text. That means we need to concatenate the logical operator to the cell we wish to reference.

=COUNTIF(E5:G9, ">"&E9)

This formula will return a value of 3, because there are only three times a category outperformed Services in 2020 (it was the iPhone category all three years).