Table of Contents
The COUNTIF function in Excel is a useful tool for easily counting the number of cells in a range that meet a specific criteria. This function allows you to specify a condition or criteria, and it will then count the number of cells within a given range that meet that condition. This can be particularly helpful when dealing with large amounts of data and wanting to quickly determine how many cells meet a certain requirement. By using the COUNTIF function, you can efficiently and accurately analyze your data and make informed decisions based on the results.
- =COUNTIF(Where do you want to look?, What do you want to look for?)
For example:
- =COUNTIF(A2:A5,”London”)
- =COUNTIF(A2:A5,A4)
COUNTIF(range, criteria)
| Argument name | Description |
|---|---|
| range (required) | The group of cells you want to count. Range can contain numbers, arrays, a named range, or references that contain numbers. Blank and text values are ignored. Learn how to select ranges in a worksheet. |
| criteria (required) | A number, expression, cell reference, or text string that determines which cells will be counted. For example, you can use a number like 32, a comparison like “>32”, a cell like B4, or a word like “apples”. COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria. |
Examples
To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.
| Data | Data |
|---|---|
| apples | 32 |
| oranges | 54 |
| peaches | 75 |
| apples | 86 |
| Formula | Description |
| =COUNTIF(A2:A5,”apples”) | Counts the number of cells with apples in cells A2 through A5. The result is 2. |
| =COUNTIF(A2:A5,A4) | Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1. |
| =COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3) | Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function. |
| =COUNTIF(B2:B5,”>55″) | Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2. |
| =COUNTIF(B2:B5,”<>”&B4) | Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,”<>75″). The result is 3. |
| =COUNTIF(B2:B5,”>=32″)-COUNTIF(B2:B5,”<=85″) | Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (<) or equal to (=) 85 in cells B2 through B5. The result is 1. |
| =COUNTIF(A2:A5,”*”) | Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4. |
| =COUNTIF(A2:A5,”?????es”) | Counts the number of cells that have exactly 7 characters, and end with the letters “es” in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2. |
Common Problems
| Problem | What went wrong |
|---|---|
| Wrong value returned for long strings. | The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters. To match strings longer than 255 characters, use the CONCATENATE function or the concatenate operator &. For example, =COUNTIF(A2:A5,”long string”&”another long string”). |
| No value returned when you expect a value. | Be sure to enclose the criteria argument in quotes. |
| A COUNTIF formula receives a #VALUE! error when referring to another worksheet. | This error occurs when the formula that contains the function refers to cells or a range in a closed workbook and the cells are calculated. For this feature to work, the other workbook must be open. |
Note: The COUNTIF function will not count cells based on cell background or font color. However, Excel supports User-Defined Functions (UDFs) using the Microsoft Visual Basic for Applications (VBA) operations on cells based on background or font color. Here is an example of how you can Count the number of cells with specific cell color by using VBA.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
Cite this article
stats writer (2024). How can I use the COUNTIF function in Excel to count the number of cells in a range that meet a specific criteria?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-countif-function-in-excel-to-count-the-number-of-cells-in-a-range-that-meet-a-specific-criteria/
stats writer. "How can I use the COUNTIF function in Excel to count the number of cells in a range that meet a specific criteria?." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-countif-function-in-excel-to-count-the-number-of-cells-in-a-range-that-meet-a-specific-criteria/.
stats writer. "How can I use the COUNTIF function in Excel to count the number of cells in a range that meet a specific criteria?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-countif-function-in-excel-to-count-the-number-of-cells-in-a-range-that-meet-a-specific-criteria/.
stats writer (2024) 'How can I use the COUNTIF function in Excel to count the number of cells in a range that meet a specific criteria?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-countif-function-in-excel-to-count-the-number-of-cells-in-a-range-that-meet-a-specific-criteria/.
[1] stats writer, "How can I use the COUNTIF function in Excel to count the number of cells in a range that meet a specific criteria?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the COUNTIF function in Excel to count the number of cells in a range that meet a specific criteria?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.