COUNTIF function in Excel
The COUNTIF function in Excel calculates the number of cells that meet a particular condition. This page provides many simple COUNTIF function example for better understanding.
💎 Numeric Criteria
Use the COUNTIF function in Excel to count cells that match a specific value or meet a condition, such as being greater than or equal to a number.
1. The COUNTIF function shown below counts how many cells contain the value 20.
2. The COUNTIF formula below returns the same result.
3. The COUNTIF function shown below counts how many cells contain values greater than or equal to 10.
4. The following COUNTIF formula returns the same result.
Explanation: The & operator combines the ‘greater than or equal to’ symbol (≥) with the value in cell C1.
5. The COUNTIF function shown below counts how many cells do not contain the value 7.
6. The following COUNTIF functions count the number of cells with the value 3 or 7.
💎 Text Tricks (count cells with content)
Use the COUNTIF function in Excel along with some simple methods to count cells that contain specific text. Always enclose text in double quotation marks.
1. The COUNTIF function calculates the total number of cells that contain the exact text “star”.
2. Using COUNTIF, you can find the number of cells that contain a star and one more character. A question mark (?) matches exactly one character.
3. The COUNTIF function below counts all cells that start with the word “star” and can have any characters after it. The asterisk (*) symbol represents a sequence of zero or more characters.
4. The COUNTIF function below counts all cells that have the word “star” anywhere in them.
5. The COUNTIF function calculates the number of cells that contain text.
💎 Count Booleans
In Excel, COUNTIF can help you count TRUE and FALSE (Boolean) values.
1. The COUNTIF function counts how many cells have the value TRUE.
2. Use the COUNTIF function below to calculate the number of cells that hold the Boolean FALSE.
💎 Count Errors
Use COUNTIF in Excel to count certain errors.
1. The COUNTIF function shown below counts how many cells have the #NAME? error.
2. Using the array formula shown below, you can determine the total count of errors within a specific range of cells.
Note: To execute an array formula correctly, use the key combination CTRL + SHIFT + ENTER. Excel adds the curly braces {}. In Excel 365 or Excel 2021, just press Enter to finish. You won’t see curly braces. For detailed instructions on creating this array formula, see our page on Counting Errors.
💎 And Criteria
Counting with And criteria in Excel is easy. The COUNTIFS function (with the letter S at the end) in Excel counts cells that meet two or more conditions. (countif for multiple criteria)
1. For example, use the COUNTIFS function to find how many rows contain Google and Stanford.
💎 Or Criteria
Using “OR” to count in Excel is sometimes tricky.
1. The COUNTIF functions below calculate how many cells in the column contain the words “Google” or “Facebook.” No rocket science so far.
2. If you want to count rows that have either “Google” or “Stanford” in two columns, you cannot just use the COUNTIF function twice (see the picture below).
Note: If a row has both “Google” and “Stanford,” it is counted twice, but it should only be counted once. 4 is the answer we are looking for.
3. The array formula below does the trick.
Note: To execute an array formula correctly, use the key combination CTRL + SHIFT + ENTER. Excel adds the curly braces {}. In Excel 365 or Excel 2021, just press Enter to finish. You won’t see curly braces. For detailed instructions on creating this array formula, see our page on Counting with Or Criteria.
💎 More about COUNTIF
The COUNTIF function is a great function. Let’s look at some more interesting examples.
1. The COUNTIF function illustrated below utilizes a named range. The named range ‘Ages’ points to the cells from A1 to A6.
2. The COUNTIF function counts how many cells have values less than the average age, which is (32.2).
3. Use COUNTIFS to count cells that fall between two numbers. (note the “S” at the end).
4. Use the COUNTIF function to see how many times each number appears in the Ages range.
Note: B2 uses the formula =COUNTIF(Ages,A2). B3 uses =COUNTIF(Ages, A3) and continues similarly.
5. Add the IF function to see if there are any duplicate values.
Tip: In Excel, you can find and highlight duplicates by using COUNTIF along with conditional formatting.
💎 Count Magic
You cannot use COUNTIF to count each occurrence of a word inside a cell or cells. All we need is a little magic!
1. Use this formula to find how many times “dog” shows up in cell A1.
2. The formula below tells you how many times “dog” shows up in the range A1 to A2.
For more details on these formulas, visit our page about counting words.
2/10 Completed! Learn more about count & sum functions ➝
Next Chapter: Logical Functions