How to use the COUNTIF function
The COUNTIF function calculates the number of cells that is equal to a condition.
Formula in cell D3:
Lucy is found twice, in cell B3 and B7, the function returns 2 in cell D3.
Excel Function Syntax
COUNTIF(range, criteria)
Arguments
range  Required. The cell range you want to count the cells meeting a condition. 
criteria  Required. The condition that you want to count. 
Comments
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.
Update!
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 occured, however, it will disappear as soon as you click outside the formula range.
Table of Contents
 Example 1  Count with single criterion
 Example 2  Count cells larger/less than a criterion
 Example 3  Count cells containing a text string
 Example 4  Absolute and relative cell reference
 Example 5  Array formula
 Download example file
Example 1  Count using a single condition
The following formula in cell D5 counts the number of cells within cell range C6:C13 that meet criterion "Lucy":
Lucy is hardcoded into the formula in this example, however, you are not required to write the actual value in the formula. You can use a cell reference instead if you like.
Hardcoded values means that the formula contains written 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.
COUNTIF(C6:C13, "Lucy")
Cell reference C6:C13 is a relative cell reference meaning it will change if you copy the cell (not the formula) and paste 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, that 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 the 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.
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.
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".
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.
Example 5  Array formula
Array formula in cell range C6:C20:
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
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.
Articles with the 'COUNTIF' Function
The following 7 articles have formulas that contain the COUNTIF function.
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference [โฆ]
How to create a list of random unique numbers
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Extract shared values between two columns
Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows [โฆ]
Use IF + COUNTIF to perform numerous conditions
The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF [โฆ]
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and [โฆ]
How to use the COUNTIF function to count not blank cells
The COUNTIF function is very capable of counting nonempty values. Column B above have a few blank cells, they are [โฆ]
Functions in 'Statistical'
The COUNTIF function function is one of many functions in the 'Statistical' category.
How to use the AVEDEV function
The AVEDEV function calculates the average of the absolute deviations of data points from their mean. The absolute deviation from the [โฆ]
How to use the AVERAGE function
Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and [โฆ]
How to use the AVERAGEA function
The AVERAGEA function returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to [โฆ]
How to use the AVERAGEIF function
The AVERAGEIF function returns the average of cell values that are valid for a given condition. Formula in cell D3: [โฆ]
How to use the AVERAGEIFS function
The AVERAGEIFS function returns the average of cell values that evaluates to TRUE for multiple criteria. Formula in cell F6: =AVERAGEIFS(D3:D8,B3:B8,F3,C3:C8,G3) [โฆ]
How to use the BETA.DIST function
The BETA.DIST function calculates the beta distribution. It represents outcomes in the form of probabilities. This function was introduced in Excel [โฆ]
How to use the BETA.INV function
The BETA.INV function calculates the inverse of the cumulative beta distribution. This function has replaced the BETA.INV function and was [โฆ]
How to use the BINOM.DIST function
The BINOM.DIST function calculates the individual term binomial distribution probability, use this function when the success probability is constant through [โฆ]
How to use the BINOM.INV function
The BINOM.INV function calculates the minimum value for which the binomial distribution is equal to or greater than a given [โฆ]
How to use the CHISQ.DIST function
The CHISQ.DIST function calculates the probability of the chisquared distribution. Use this function to check if a hypothesize is valid. It [โฆ]
How to use the CHISQ.DIST.RT function
The CHISQ.DIST.RT function was introduced in Excel 2010 and calculates the righttailed probability of the chisquared distribution. It has replaced the [โฆ]
How to use the CHISQ.INV function
The CHISQ.INV function was introduced in Excel 2010 and calculates the inverse of the lefttailed probability of the chisquared distribution. [โฆ]
How to use the CHISQ.INV.RT function
The CHISQ.INV.RT function was introduced in Excel 2010 and calculates the inverse of the righttailed probability of the chisquared distribution. [โฆ]
How to use the CHISQ.TEST function
The CHISQ.TEST function calculates the test for independence, the value returned from the chisquared statistical distribution and the correct degrees [โฆ]
How to use the CONFIDENCE.NORM function
The CONFIDENCE.NORM function calculates the confidence interval for a population mean. Formula in cell C7: =CONFIDENCE.NORM(C3,C4,C5) Excel Function Syntax CONFIDENCE.NORM(alpha,standard_dev,size) [โฆ]
How to use the CONFIDENCE.T function
The CONFIDENCE.T function calculates the confidence range for a population mean using a Student's t distribution. Formula in cell C7: [โฆ]
How to use the CORREL function
The CORREL function calculates the correlation between two groups of numbers. Formula in cell B12: =CORREL(C3:C9, D3:D9) The value in cell [โฆ]
The COUNT function counts all numerical values in an argument, it allows you to have up to 255 arguments. Blank [โฆ]
How to use the COUNTA function
The COUNTA function counts the nonempty or blank cells in a cell reference. The picture above demonstrates the COUNTA function [โฆ]
How to use the COUNTBLANK function
The COUNTBLANK function counts empty or blank cells in a range. The picture above demonstrates the COUNTBLANK function entered in [โฆ]
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
How to use the COUNTIFS function
Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.
How to use the COVARIANCE.P function
The COVARIANCE.P function calculates the covariance meaning the average of the products of deviations for each pair in two different [โฆ]
How to use the COVARIANCE.S function
The COVARIANCE.S function calculates the sample covariance meaning the average of the products of deviations for each pair in two [โฆ]
How to use the EXPON.DIST function
The EXPON.DIST function calculates the exponential distribution representing an outcome in the form of probability. This function was introduced in Excel [โฆ]
How to use the F.DIST function
The F.DIST function calculates the F probability for two tests. This function was introduced in Excel 2010 and has replaced [โฆ]
How to use the F.DIST.RT function
The F.DIST.RT function calculates the righttailed F probability for two tests. This function was introduced in Excel 2010 and has replaced [โฆ]
How to use the F.TEST function
The F.TEST function calculates the twotailed probability from an Ftest, the value shows if the variances from two data sets [โฆ]
How to use the FORECAST.LINEAR function
The FORECAST.LINEAR function calculates a value based on existing x and y values using linear regression. Use this function to [โฆ]
How to use the FREQUENCY function
Returns how many times values exist in a given range. Note, this function returns an array of values.
The GAMMA function calculates the GAMMA value. It is an extension of the factorial function, the argument is shifted down [โฆ]
How to use the GAMMA.DIST function
The GAMMA.DIST function calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution. This [โฆ]
How to use the GEOMEAN function
The GEOMEAN function calculates the geometric mean. It represents the typical value of a set of numerical values based on the [โฆ]
How to use the GROWTH function
The GROWTH function returns estimated exponential growth based on given data. It calculates the yvalues for new xvalues based on [โฆ]
How to use the INTERCEPT function
The INTERCEPT function returns a value representing the yvalue where a line intersects the yaxis. The line is calculated using [โฆ]
The LARGE function calculates the kth largest value from an array of numbers. Use the LARGE function, for example, to extract [โฆ]
How to use the LINEST function
The LINEST function returns an array of values representing the parameters of a straight line based on the "least squares" [โฆ]
How to use the LOGEST function
The LOGEST function returns an array of values representing the parameters of an exponential curve that fits your data, based [โฆ]
How to use the LOGNORM.DIST function
The LOGNORMDIST function calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev. This [โฆ]
The MAX function allows you to calculate the largest number in a cell range. The formula in cell D3 extracts [โฆ]
How to use the MAXIFS function
The MAXIFS function allows you to calculate the highest value based on a condition or criteria. Formula in cell G4: [โฆ]
How to use the MEDIAN function
The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a [โฆ]
The MIN function allows you to retrieve the smallest number in a cell range. The formula in cell D3 extracts [โฆ]
The MINA function returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE [โฆ]
How to use the MINIFS function
The MINIFS function calculates the smallest value based on a given set of criteria. Formula in cell E3: =MINIFS(C3:C10,B3:B10,"A") The [โฆ]
How to use the MODE.MULT function
The MODE.MULT function calculates the most frequent number in a cell range. It will return multiple numbers if they are equally [โฆ]
How to use the MODE.SNGL function
The MODE.SNGL function calculates the most frequent value in an array or cell range. Excel Function Syntax MODE.SNGL(number1,[number2],...) Arguments number1 [โฆ]
How to use the NORM.DIST function
The NORM.DIST function calculates the normal distribution for a given mean and standard deviation. Formula in cell C7: =NORM.DIST(C2,C3,C4,C5) Excel [โฆ]
How to use the NORM.INV function
The NORM.INV function calculates the inverse of the normal cumulative distribution for a given mean and standard deviation. Formula in [โฆ]
How to use the PERCENTRANK.EXC function
The PERCENTRANK.INC function calculates the percent rank of a given number in a data set. This function was introduced in [โฆ]
How to use the PERCENTRANK.INC function
The PERCENTRANK.INC function calculates the percent rank of a given number compared to the whole data set. The image above [โฆ]
How to use the PERMUT function
The PERMUT function returns the number of permutations for a set of elements that can be selected from a larger [โฆ]
How to use the PERMUTATIONA function
The PERMUTATIONA function returns the number of permutations for a specific number of elements that can be selected from a [โฆ]
The PHI function calculates a number of the density function for a standard normal distribution. Formula in cell C3: =PHI(B3) Excel [โฆ]
The PROB function calculates the probability that values in a range are between a given lower and upper limit. Probability [โฆ]
How to use the QUARTILE.EXC function
The QUARTILE.EXC function returns the quartile of a data set, use the QUARTILE.EXC function to divide data into groups. This [โฆ]
How to use the QUARTILE.INC function
The QUARTILE.INC function returns the quartile of a data set, based on percentile values from 0..1, inclusive. Use the Quartiles.inc [โฆ]
How to use the RANK.AVG function
The RANK.AVG function returns the rank of a number in a list of numbers. The number returned indicates its size [โฆ]
How to use the RANK.EQ function
The RANK.EQ function calculates the rank of a number in a list of numbers, based on its position if the [โฆ]
The SKEW function calculates the skewness of a group of values with an asymmetric tail from its mean value. Formula in [โฆ]
The SLOPE function calculates the slope of the linear regression line through coordinates. Formula in cell B10: =SLOPE(B3:B7,C3:C7) Excel Function [โฆ]
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
How to use the STANDARDIZE function
The STANDARDIZE function calculates a normalized value from a distribution characterized by mean and standard_dev. Formula in cell C5: =STANDARDIZE(C2, [โฆ]
How to use the STDEV.P function
The STDEV.P function returns standard deviation based on the entire population. The standard deviation is how widely numbers are distributed [โฆ]
How to use the STDEV.S function
The STDEV.S function returns standard deviation based on a sample of the entire population. The standard deviation is how widely [โฆ]
How to use the STDEVA function
The STDEVA function estimates the standard deviation from a sample of values. Standard deviation shows how much the values differ [โฆ]
How to use the STDEVPA function
The STDEVPA function returns the standard deviation based on the entire population, including text and logical values. The standard deviation [โฆ]
The TREND function calculates values along a linear trend. Fits a straight line (using the method of least squares) to [โฆ]
How to use the TRIMMEAN function
The TRIMMEAN function calculates the mean of the interior of a data set. The function excludes a percentage of data [โฆ]
The VAR.P function returns the variance based on the entire population. The function ignores logical and text values. Variance shows [โฆ]
The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and [โฆ]
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.