Business, Education, Technology/Software

How to use COUNTIF in Excel: guide and examples

excel

Clear step by step guide on how to use COUNTIF explains COUNTIF structure and formulas, and gives illustrated examples of use.

Find more education guides, tips and advice

The COUNTIF function in Microsoft Excel finds the number of cells in a range that match a single specified criterion. For example, you can count all cells that start with a specific letter or all cells that contain a number that is greater or less than a specified number. If you z. 

For example, if you have a worksheet with a number of tasks in column A and the first name of the person to whom the task is assigned in column B, you can use the COUNTIF function to find out how often a person’s name appears in column B, and this way you can see how many tasks are assigned to this person. Example:

= COUNTIF (B2: B25; "Andrea")

In Excel, the COUNTIF function can be used to count a number of rows with specific keywords. The principle may sound a little complicated. However, as you go through this guide, you will see that it is not that difficult to use. Incidentally, the cells are only examined for keywords, not for font or background color. The COUNTIF function is closely related to the SUMIF function. You can find out more about the SUMIF function in Excel here.

  • Structure of the COUNTIF function in Excel
  • Use the COUNTIF function in Excel

Structure of the COUNTIF function in Excel

COUNTIF(Search Area;Search Criterion)

Search Area: Here you need to enter cells in which you want to search. These can be individual cells (“A2, A3, A5”) or entire sections (“A2: A5”). In this context, the colon means that all cells from cell A2 to cell A5 are searched (i.e. A2, A3, A4 and A5).

Search criterion: Here you have to enter what you are looking for. This can be a specific catchphrase, such as “New York” in a city list. If you do not have specific cells analyzed, but rather a defined search term, as here, you have to put it in quotation marks.
Alternatively, the content of a cell can also be used as a search term. Approximately A2 – then you will be shown how many cells including cell A2 the search term occurs in. Here, however, the cell name must be used without quotation marks.

How to use COUNTIF function in Excel

In the course of the instructions, we will be guided by an example: There is a list of locations of a company (column A) and how much this location has earned per order (column B).

How to use COUNTIF

If you want to know how many orders the location in New Yorkhas taken, for example , you have to enter:

=COUNTIF(A2:A10;"New York")

The result in the example would then be 2. You don’t even have to enter the search term manually. Just find a cell that already has it, for example:

=COUNTIF(A2:A10;A2)

The number of hits for “New York” is output here as well, since Hanover is the content of cell A2. So the result is again 2.

The input of the characters ‘?’ and ‘*‘ 

A question mark represents exactly one unknown position for a character, an asterisk represents an unknown number of characters. Instead of New York you can also use “New ????” enter. You can use the question mark and the asterisk if you are not completely familiar with a search term and would like to use a wildcard.

It is also possible to add different numbers of hits. If you now want to add up the number of orders at the Kiel location and the Hanover location, simply do it with two count-if functions that are connected with a plus . It does not matter whether you enter the keyword or the cell with the keyword content.

By the way, this also works with subtraction : Instead of a plus sign, just use a minus sign .

=COUNTIF(A2:A10;A2)+COUNTIF(A2:A10;"Kiel")
=COUNTIF(A2:A10;A2)-COUNTIF(A2:A10;”Kiel”)
How to use COUNTIF

For the Search within numerical series , the prove greater than / less-than symbol as appropriate. For example, you can display the number of hits for orders greater than € 5,000 by entering the following:

=COUNTIF(B2:B10;">5000")

Please note here that numbers must also be put in quotation marks. Instead of > you can also use < . If you write an equal sign next to it, you can also include the specified value. The following would therefore look for orders that brought in € 5,000 or more :

=COUNTIF(B2:B10;">=5000")