Excel COUNTIF Function


What does it do?

The Excel COUNTIF function counts together the number of cells that meet a given criterion.  The criterion can be a text string, number or logical expression, i.e. GREATER THAN, LESS THAN, NOT EQUAL TO, EQUAL TO, GREATER THAN or EQUAL TO, LESS THAN or EQUAL TO and EQUAL TOExpressions use the logical operators <, >, <>, = for numerical criterion. For text, use the wildcards, asterisk (*) or question mark (?) for partial matching.


Syntax Explained

Excel COUNTIF function in the formula bar showing syntax.

Range: This is the range where you want to see if your matching Criteria appears in.

Criteria: This is the condition to test for in the range, when it is met/found, it will be counted into the result.


Examples

1. COUNTIF to match a text string.

Example of an Excel COUNTIF function counting the number of times a text string appears in a range.

Let’s begin with a basic version by looking at the range in blue for the criteria “Red”, here we can see a total of 2 exists in the range, hey presto, it works!  You just saved a bunch of time!  😊

2. COUNTIF for GREATER THAN, LESS THAN, NOT EQUAL TO, EQUAL TO, GREATER THAN or EQUAL TO, LESS THAN or EQUAL TO and EQUAL TO.

Example of an Excel COUNTIF function counting the number of times a logical test is found to be true in a range.

If you want to find out the COUNT of cells in a range where they are GREATER THAN, LESS THAN, NOT EQUAL TO, EQUAL TO, GREATER THAN or EQUAL TO, LESS THAN or EQUAL TO and EQUAL TO.  You can use the logical operators (<, >, <> and =) directly into the function.  Ensure these are enclosed in quotes since they are considered text.

3. COUNTF by partial match of a text string using the Asterisk symbol (*).

Example of an Excel COUNTIF function counting the number of times a text string is partially matched in a range using the asterisk wildcard symbol.

Sometimes you might need to COUNT only partial matches of a full or part of a word in a Range.  In the example above this is demonstrated by specific placement of the Asterisk * symbol.

4. COUNTF by wildcard using the question mark symbol (?).

Example of an Excel COUNTIF function counting the number of times a text string is partially matched in a range using the question mark wildcard symbol.

Above you can see the results are picking up a total of 2 for each formula.  You can add multiple question marks (?) for as many missing characters as needed, each question marks represents 1 missing character.