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 159 articles contain 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 formulas that extract values that exist only in one column out of two columns. There are text […]
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 […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
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 […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
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 […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
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. […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
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 […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
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 article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
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 […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Filtering unique distinct text values and sorting 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 B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
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, […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
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 Filter unique distinct values based on a date […]
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 […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
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 […]
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]
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 […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 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 […]
Functions in this article
Functions in 'Statistical' category
The COUNTIF function function is one of many 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.
[...] 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.