Excel COUNTIF formula
Appears from version: 5.0
One of the most important versions of Excel is version 5.0 from 1994, when, in addition to VBA, PivotTable functionality, the set of functions was also significantly expanded, now the COUNTIF function is particularly interesting to us, which is explained in detail on the Office page for user support.
In many cases, it is necessary to count values in cells that meet some logical criterion.
We use the COUNTIF function, one of the statistical functions to count the cells that meet the criteria.
Syntax
COUNTIF(range, criteria)
- Range (required) – The group of cells we want to count. A range can contain numbers, strings, a named range, or references that contain numbers. blank and text values are ignored.
- Criteria (required) – A number, expression, cell reference, or text string that specifies which cells to count. COUNTIF uses only one criterion. If you want to use more criteria, use the COUNTIFS function.
Examples of use
COUNTIF is a slightly more complex formula, which can contain various criteria. Specifically, in the first example, it is the word in cell E6, which means that it counts the cells in which the exact value is found. In this case the word Likely appears five times and the result is 5. The formula is: =COUNTIF($E$6:$E$20;$E$6)
The second example tells how many cells have a value greater than the defined value (of the number 2). Otherwise, the same applies, if we use the less than sign, we can see how many cells have a value less than the defined one. The formula is: =COUNTIF(B1:B7;”>2″) (result is 5)
An example similar to the previous one is the third example, only here we can count a value that is greater than or equal to the defined one. When it comes to less than or equal to, the procedure is the same. The formula is: The formula is: =COUNTIF(B1:B7;”>=2″) (result is 7)
It is a somewhat more complex formula that shows how many cells have a value greater than two and less than seven. The formula is: =COUNTIF(B1:B7;”<7″)-COUNTIF(B1:B7;”<2″) (result is 3)
In the last example for the Countif function, the number of cells that contain a specific word is shown. The asterisk replaces any other symbol. That’s why we can combine it with other words. In the example in the picture, we count every cell that contains the word report, whether it is at the beginning, end, or middle.
Remark
The COUNTIF formula receives an error #VALUE! when referencing another worksheet This error occurs when a formula containing a function references cells or a range in a closed workbook, and the cells are being calculated. Another workbook must be open for this feature to work.