How to use the COUNTIF function
The COUNTIF function calculates the number of cells that meet a given condition.
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.
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 - Absolute and relative cell reference
- How to use the COUNTIF function with multiple values (array formula)
- 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")
becomes
COUNTIF({"Lucy"; "Elizabeth"; "Martin"; "Andrew"; "Lucy"; "Jennifer"; "Geoffrey"; "Abraham"}, "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 - Absolute and relative cell reference
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.
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:
becomes
=COUNTIF({"Watermelon"; "Banana"; "Orange"; "Kiwi"; "Lemon"; "Apple"; "Apricot"; "Banana"; "Pear"; "Apple"; "Pineapple"; "Banana"; "Pear"; "Pear"; "plum"},"plum") and returns 1 in cell C20
7. Example 5 - Array formula
Array formula in cell range C6:C20:
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
becomes
=COUNTIF({"Watermelon"; "Banana"; "Orange"; "Kiwi"; "Lemon"; "Apple"; "Apricot"; "Banana"; "Pear"; "Apple"; "Pineapple"; "Banana"; "Pear"; "Pear"; "plum"},{"Watermelon"; "Banana"; "Orange"; "Kiwi"; "Lemon"; "Apple"; "Apricot"; "Banana"; "Pear"; "Apple"; "Pineapple"; "Banana"; "Pear"; "Pear"; "plum"})
and returns {1; 3; 1; 1; 1; 2; 1; 3; 3; 2; 1; 3; 3; 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 following formula won't work in cell F6:
=COUNTIF(LEN(C6:C13), F5)
Excel shows a dialog box containing an error message.
The following formula works:
=SUMPRODUCT((LEN(C6:C13)=F5)*1)
8.1 Explaining formula in cell F6
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)
becomes
LEN({"Lucy"; "Elizabeth"; "Martin"; "Andrew"; "Lucy"; "Jennifer"; "Geoffrey"; "Abraham"})
and 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
becomes
{4; 9; 6; 6; 4; 8; 8; 7}=F5
becomes
{4; 9; 6; 6; 4; 8; 8; 7}=6
and returns
{FALSE; FALSE; TRUE; TRUE; FALSE; 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
becomes
{FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE}*1
and 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.
'COUNTIF' Function examples
The following 147 articles have formulas containing the COUNTIF function.
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. […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
Lookup with criteria and return records.
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]
Table of Contents Extract shared values between two columns Extract shared values between two columns - Excel 365 Extract shared […]
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]
The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]
Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]
Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]
Table of Contents Find closest value How to enter an array formula Find closest value - Excel 365 Find closest […]
Table of Contents Extract largest duplicate number Extract largest duplicate number - Excel 365 Extract smallest duplicate number Extract smallest […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]
Table of Contents How to rank text uniquely without duplicates How to rank uniquely based on a condition Get Excel […]
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]
This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]
This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]
Functions in 'Statistical' category
The COUNTIF function function is one of many functions in the 'Statistical' category.
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.
[...] 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.