Excel: Count if you just use it - how it works
With the formula Count if you can count entries in a list in Excel that meet certain criteria. For example, you can count values that are less than 1, or quickly determine how often terms appear in a list. A practical mechanism - but you have to use it correctly.
Counting if in Excel: Here's how
The Count if function in Excel counts cells whose content meets certain criteria. To do this, you must specify an area in which to search and determine a criterion.
- The formula is: "= COUNTIF (range; search criteria)" (without quotation marks).
- Range: Here you select the cell range that contains the values or terms that you want to count.
- Search criteria: You can specify one or more search criteria that Excel uses to search for the values or terms to be counted.
CountingIf: An example
The easiest way to explain the counting formula is with an example - you can see it in our screenshot.
- In our example, we have Excel count how often the term "chocolate" appears in a list of ice cream flavors.
- Column A contains 20 types of ice cream, some of them multiple. The search criterion is in cell D2, cell D3 shows how often the term occurs. Enter the counting if formula here.
- In cell D2, we enter chocolate to count the number of times the term appears in the list.
- In cell D3 we enter = COUNTIF (A1: A20; D2). Excel now counts how often the term appears in cell D2 in the range from A1 to A20.
- The result is 5 and is output in cell D3.
- You can also write the term with quotation marks directly in the counting-if formula - in our example = COUNTING-IF (A1: A20; "chocolate").
Search criteria in counting if
The search criteria does not have to be a term, for example you can count how many numbers in a list are less than 5.
- Instead of entering the search criterion in a cell and referring to it in the formula, you can also enter it directly in the formula.
- Use quotes. For example, "= COUNTIF (A1: A20;" <5 ")" counts how many numbers in the range A1 to A20 are less than 5.
Tips for dealing with counting
- You can select a range of cells with the mouse. If you want to enter the range in the formula, click on the first cell of the range and drag the selection with the mouse button held down to the last cell.
- If you have a particularly large area, marking with the mouse is cumbersome. Click on the first cell and hold down [Shift] and [Ctrl]. Now you can use the arrow keys to mark a contiguous area to the right or down.
