How to use the COUNTIF function
What is the COUNTIF function?
The COUNTIF function calculates the number of cells that meet a given condition.
The image above shows names in cell range B3:B10, the formula in cell D3 counts the cells in B3:B10 equal to a specified condition. The condition in this example is "Lucy".
Formula in cell D3:
Lucy is found twice, in cell B3 and B7, the function returns 2 in cell D3.
The condition is not case sensitive meaning condition "lucy" will also return 2 demonstrated in the image above.
Note that you can use multiple conditions in the second argument, however, you need to enter the formula as an array formula. Excel 365 subscribers don't need to enter the formula as an array formula.
Table of Contents
- COUNTIF Function Syntax
- COUNTIF Function Arguments
- COUNTIF Function - How to count cells equal to a condition?
- COUNTIF Function - Count cells larger/less than a criterion
- COUNTIF Function - Count cells containing a text string - partial match (wildcard)
- COUNTIF Function - running count
- How to use the COUNTIF function with multiple values - create an array of values containing the count of each value
- COUNTIF Function - How to count cells containing x number of characters?
- COUNTIF Function - Dynamic array formula (Excel 365)
- Get example file
1. COUNTIF Function Syntax
COUNTIF(range, criteria)
2. COUNTIF Function Arguments
range | Required. The cell range you want to count the cells meeting a condition. |
criteria | Required. The condition that you want to count. |
3. How to count cells equal to a condition?
The following formula in cell F6 counts the number of cells within cell range C6:C13 that equals the condition specified in cell F5.
You can also use a value instead of a cell reference inside the formula.
Name "Lucy" is hardcoded into the formula in this example. Hardcoded values mean that the formula contains literal values and cell references are not being used.
The downside is that you need to change the formula if you need to use another value.
Cell reference C6:C13 is a relative cell reference meaning it will change if you copy the cell (not the formula) and paste it to other cells. Add dollar signs, to prevent this behavior, which will lock the cell reference. Example, $C$6:$C$13.
To toggle between relative and absolute cell references you select the cell reference and then press function key F4. To learn more, read this article:
How to use absolute and relative references
COUNTIF(C6:C13, "Lucy")
Note that the array has semicolons as a separating character, which shows that the values are located on a row each.
You can easily convert a cell reference to hardcoded values, select the cell range and then press function key F9. That will instantly convert the cell range to constants.
COUNTIF({"Lucy"; "Elizabeth"; "Martin"; "Andrew"; "Lucy"; "Jennifer"; "Geoffrey"; "Abraham"}, "Lucy")
returns 2 in cell D5. I have bolded the matching values to show that the correct value is 2.
4. Example 2 - Count cells larger/less than a criterion
The following formula in cell D5 counts the number of cells within cell range C6:C13 that is larger than or equal to 500. The image above has six numbers in cell range C6:C14 that are larger than or equal to 500.
The formula in cell D5 returns 6, the following six numbers 512, 674, 960, 796, 940 and 848 are larger than 500.
You can use these operators:
- < less than
- > larger than
- = equal sign
- <= less than or equal to
- >=larger than or equal to
- <> not equal to
Remember to use double quotes when you combine a number with an operator.
5. Example 3 - Count cells containing a text string
The following formula in cell D5 counts the number of cells within cell range C6:C13 that contains the text string "apple":
The asterisk matches no characters, any single character or any multiple characters. That is why "*apple*" matches "Orange, Apple", note also that the COUNTIF function is not taking into account upper and lower letters.
There is one more wildcard character you can use which is the question mark. The question mark allows you to match any single character.
The formula above utilizes this condition "*appl?" and matches two cells in cell range C6:C13, displayed in the above image. They are "Orange, Apple" and "Kiwi, Pineapple".
6. Example 4 - running count
With clever use of absolute and relative cell references you can build formulas containing cell references that expand automatically when you copy the cell and paste to cells below. This example shows a "running count" meaning as the formula is copied to cells below as far as needed the calculations include new values next to the formula cell making it "running".
Formula in cell C6:
$B$6:B6 is a cell reference to cell B6. When the cell is copied to cells below, the cell reference changes. The first part $B$6 i always locked to cell B6, the last part B6 changes. Cell range $B$6:B6 "grows" when you copy the cell.
This technique is used in this popular post:Â Filter unique distinct values
In cell C20:
returns 1 in cell C20
7. Example 5 - create an array of values containing the count of each value
This example shows how you can use the COUNTIF function to count each value in a cell range then create an array as large as the source data range containing each count. This technique is very useful for determining unique, unique distinct , or duplicate values in a given cell range.
Array formula in cell range C6:C20:
The image above shows example data in cell range B6:B20, it contains random fruits, some are duplicates, some are unique. The array shown in cell range C6:C20 corresponds to the same item in B6:B20 meaning the number represents the count/frequency of that particular value.
For example, the first value is "Watermelon" in B6, the corresponding value in cell C6 is 1. It represents the total count of "Watermelon" in cell range B6:B20. 1 means that it is a unique value, in other words it exists only once.
The amazing thing is that Excel calculates this array in only one formula, this makes it possible to do some seriously complicated stuff with arrays.
7.1 How to enter an array formula
- Â Select cell range C6:C20
- Copy / Paste formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys
7.2 Explaining the array formula
The COUNTIF function counts the number of cells within a range that meet a single criterion. In this example, I am using multiple values in the criteria argument.
Each value is in the criteria argument is used as a criterion and the returning array has the same number of values as the criteria argument.
The technique described here is used in this popular post:Â Count unique distinct values
returns {1; 3; ... ; 1} in cell range C6:C20.
8. How to count cells containing x number of characters?
Here is one downside with the COUNTIF function, you can't use other functions in the range argument. There are rare exceptions, one is the OFFSET function.
The image above shows numbers in B6:B13 and names in C6:c13, the formula in cell F6 tries to count cells containing a value with a specific character count specified in cell F5. To do that we need to calculate the length of each value in C6:C13, however, the COUNTIF function does not allow us to preform additional calculations in the range argument.
The following formula won't work in cell F6:
=COUNTIF(LEN(C6:C13), F5)
Excel shows this dialog box containing an error message.
We need a workaround, the following formula works:
=SUMPRODUCT((LEN(C6:C13)=F5)*1)
It counts all cells in C6:C13 containing a value with 6 characters. There are two cells with 6 characters each, they are C8 and C9.
8.1 Explaining formula in cell F6
The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.
Step 1 - Count characters for each cell
The LEN function counts the number of characters in a cell, the LEN function returns an array of numbers if you use a cell range.
LEN(C6:C13)
returns {4; 9; 6; 6; 4; 8; 8; 7}.
Step 2 - Compare with the number in cell F5
The equal sign allows you to compare values. We want to identify cells that have the same number of characters as the specified number in cell F5.
The logical expression returns TRUE or FALSE.
LEN(C6:C13)=F5
returns {FALSE; FALSE; ...; FALSE}
Step 3 - Multiply with 1
The SUMPRODUCT can't sum boolean values, we need to convert them to their numerical equivalents. TRUE = 1, FALSE = 0 (zero).
(LEN(C6:C13)=F5)*1
returns {0;0;1;1;0;0;0;0}.
Step 4 - Sum numbers
The SUMPRODUCT function is able to add numbers in an array without the need to enter the formula as an array formula.
The formula becomes slightly larger compared to using the SUM function.
SUMPRODUCT((LEN(C6:C13)=F5)*1)
9. Dynamic array formula (Excel 365)
You no longer need to enter the formula as an array formula if you are a subscriber of Office 365, the new feature is called "Dynamic Arrays" and was introduced to Excel in January 2020.
It will automatically detect if a formula returns more than one value and will extend accordingly based on the number of values that are being returned, this is called spilling.
The image above demonstrates this behavior, the formula has extended automatically to cells below as far as needed. A blue border indicates that spilling has occurred, however, it will disappear as soon as you press with left mouse button on outside the formula range.
A #SPILL error is displayed if the destination cells are not empty. Make sure to delete cell values below and/or to the right as far as needed or enter the formula in cell that allows you to spill values below etc.
'COUNTIF' function examples
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This post explains how to lookup a value and return multiple values. No array formula required.
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Functions in 'Statistical' category
The COUNTIF function function is one of 73 functions in the 'Statistical' category.
Excel function categories
Excel categories
5 Responses to “How to use the COUNTIF function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
[...] COUNTIF(range,criteria) Counts the number of cells within a range that meet the given condition [...]
[…] COUNTIF(range,criteria) Counts the number of cells within a range that meet the given condition […]
[…] COUNTIF(range,criteria) Counts the number of cells within a range that meet the given condition […]
[…] COUNTIF(range,criteria) Counts the number of cells within a range that meet the given condition […]
I need a formula that will count the number of cells containing dates in column C1 to C10 that are less than or equal to the dates in column A1 to A10.