Simple calculation such as adding the values in a range of cells or counting the values in reach of cells is something that you would have to do when working with data in Excel. And in some cases, you may have to count only those cells that meet a specific criterion. And you can easily do that with the COUNTIF function in Excel In this tutorial, I will show you how the Excel COUNTIF function works with simple examples add a detailed explanation Let’s first look at the syntax of the COUNTIF function:
Excel COUNTIF Function Syntax
where
range is the range of cells where you want to count cells that meet the condition criteria is the condition that must be evaluated against the range of cells for a cell to be counted.
Excel COUNTIF Function Examples
Now let’s have a look at some examples that will show you how to use the COUNTIF function in Excel.
Count Cells With a Specific Text String
With the COUNTIF function, you can count all the cells that contain a specific text string. Suppose you have a dataset as shown below and you want to count all the cells that have the text Printer in it.
Here is the formula that will do this:
The above formula uses the text I specified as the second argument as the criteria and counts all the cells that have the same text (which is “Printer”) In this example, I have manually entered the criteria text, but you can also refer to a cell that contains the criteria text. Note: Criteria text in the COUNTIF formula is not case sensitive. So I can also use ‘printer’ or ‘PRINTER’, as the result would still be the same
Count Cells Value Greater than or Less than
Just like I used the COUNTIF function with text, I can also use it with cells containing numbers. Suppose I have a dataset as shown below and I want to count all the cells where the number in column B is greater than 30.
Below is the formula that will do this:
The above formula uses the greater than an operator with the number as the criteria. This tells Excel to only consider those cells where the value is more than 30. You can also use other operators such as less than (<). equal to (=), and not equal to (<>) in the COUNTIF criteria.
Count Cells that Contain Text String
While there is the COUNTA function that counts the cells that contain numbers, there is no in-built formula that can count only those cells that contain a text string. But it can easily be done using the COUNTIF function. Suppose you have a dataset as shown below and you only want to count the number of cells that are text (and ignore the numbers).
Here is the formula that will do this:
The above formula uses an asterisk (which is a wildcard character). An asterisk represents the text of any length. So this criteria would count all the cells where there is any text string (of any length). In case the cells are empty/blank or have numbers in them, then those would not be counted.
Some Additional Notes
Criteria could be a number, expression, cell reference, text, or a formula. Criteria which are text or mathematical/logical symbols (such as =,+,-,/,) should be in double-quotes. Wildcard characters can be used in criteria. There are three wildcard characters in Excel – the question mark (?), an asterisk (), and tilde (~) A question mark (?) matches any single character An asterisk matches (*) any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. Criteria are case-insensitive (“Hello” and “hello” are treated as the same).
Related Excel Functions:
Excel COUNT Function. Excel COUNTBLANK Function. Excel COUNTIFS Function Excel Functions
Count Unique Values in Excel Using COUNTIF Function. Using Multiple Criteria in Excel COUNTIF and COUNTIFS Function How to Count Cells that Contain Text Strings How to Count Colored Cells In Excel Count Characters in a Cell (or Range of Cells) Using Formulas in Excel
