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
ZÄHLENWENN(Suchbereich;Suchkriterium)
Search Area: Here you need to enter cells that you want to search in. 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 "Hanover" in a city list. If you do not have specific cells analyzed, but rather a defined search term, as is the case 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..
Use the 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).
If you now want to know how many orders the location in Hanover has taken, for example , you have to enter:
=ZÄHLENWENN(A2:A10;"Hannover")
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:
=ZÄHLENWENN(A2:A10;A2)
Here, too, the number of hits for "Hanover" is output, since Hanover is the content of cell A2. 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. So instead of Hanover you can also use "Hann ????" input. 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 this 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 .
=ZÄHLENWENN(A2:A10;A2)+ZÄHLENWENN(A2:A10;"Kiel")
The larger / smaller signs also prove to be useful for searching within series of numbers . For example, you can display the number of hits for orders greater than € 5,000 by entering the following:
=ZÄHLENWENN(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. In the following, we would look for orders that brought in € 5,000 or more :
=ZÄHLENWENN(B2:B10;">=5000")