​​​​Countif

The Excel COUNTIF function counts the number of numeric values in a given cell range if they meet the specified condition.

Syntax

=COUNTIF(Range, Condition)

Argument  Argument description
1RangeThe cell range of which the numbers are counted if they meet the condition.
2ConditionThe condition that determines which values are counted. Should almost always be enclosed in double

Example 1: Simple Excel Countif formula

A Countif formula in its simplest form may look like this:

=COUNTIF(A1:A3, ">1")
A simple COUNTIF example. Given a cell range with values 1, 2 and 3, and the condition > 1, the Countif function returns 2.
A simple COUNTIF example. Given a cell range with values 1, 2 and 3, and the condition > 1, the Countif function returns 2.

Explanation

Excel returns 2 because there are two numeric values larger than 1 in the cell range A1:A3.

Example 2: Wildcards and Countif

You can use any condition for the Condition argument of the Countif function. The condition can simply be a text, a number or a date. In such cases, Excel will check if the values are equal to the given condition value.

For example:

=(A1:A3, "Pass")

Will count all cells containing the text Pass in the range A1:A3.

But there are more ways you can create conditions. By combining values with conditional operators or with functions that return TRUE or FALSE. If you’d like to know more about these types of conditions, check out this section of our complete guide on the If function.

Finally, another thing that’s allowed is the use of wildcards. Those can be inserted in the Conditionargument.

If you don’t know what wildcards are, they are characters like ? and * that allow you to match with an approximate match instead of an exact match.

Note: for more information on wildcards, check this section about wildcards from our Find and Replace post.

Here’s an example formula in which we count how many of the email addresses are Gmail addresses. We use the asterisk (*) wildcard to match with gmail.com without regarding the first part of the address.

A Countif formula with an asterisk (*) wildcard to count the number of Gmail addresses in the cell range.
A Countif formula with an asterisk (*) wildcard to count the number of Gmail addresses in the cell range.

Explanation

The result is 3 because that is the number of email addresses in the cell range A2:A6 ending in gmail.com.

The condition *gmail.com matches with any value ending in gmail.com, regardless of what text is before that. Therefore, we match with every Gmail address and we can count how many there are.

Questions

Can I use multiple conditions with a Countif function?

Yes, you can. The easiest way to do this is by using the Countifs function, which allows for multiple (range, condition) pairs.
An alternative with one range and multiple conditions is to use the regular Sumif function, combined with logical operators (And, Or, Not, Xor).

Can an array be used instead of a cell range?

No, the Countif and Countifs functions are not compatible with arrays as input argument. In fact, none of the functions in the Ifs family are. Instead, you can use the Sumproduct function to achieve the desired result.

Is Countif case sensitive?

No, Countif is not case sensitive. So if your condition is “MAYA” you will also find Maya, mAYA and maya.

Drawbacks

The Excel Countif function has some drawbacks:

  1. We’ve mentioned it before: you cannot use arrays instead of cell ranges with the COUNTIF or COUNTIFS functions.
  2. Using Countif to match strings with more than 255 characters will produce incorrect results.
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Join List
>