## How to use the COUNTIF function

*Article last updated on March 29, 2018*

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.

### 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 with a single criterion

The following formula in cell D5 counts the number of cells within cell range C6:C13 that meet criterion "Lucy":

=COUNTIF(C6:C13, "Lucy")

### 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:

### 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":

### Example 4 - Absolute and relative cell reference

Formula in cell C6:

=COUNTIF($B$6:B6,B6)

$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:

=COUNTIF($B$6:B20,B20)

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:

=COUNTIF(B6:B20, B6:B20)

**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

=COUNTIF(B6:B20, B6:B20)

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.

### Download excel *.xlsx file

### Articles with the 'COUNTIF' Function

The following 16 articles have formulas that contain the COUNTIF function.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

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 […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Create a unique distinct list and sort based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Extract largest values from two columns

Question: How do I extract the five largest values from two or more columns? Answer: Formula in A12: =INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, […]

### 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 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 non-empty 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 EXPON.DIST function

The EXPON.DIST function returns the exponential distribution. Use EXPON.DIST to model the time between events. Formula in cell C3: =EXPON.DIST(B3,C3,D3) […]

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 LARGE function calculates the k-th largest value from an array of numbers. Use the LARGE function, for example, to extract […]

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 MODEMULT 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 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 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 […]

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 […]

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

Use the img tag, like this: <img src="Insert pic link here">

**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.