## COUNTIF function

**Table of Contents**

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

### Introduction

The **COUNTIF** function counts the number of cells within a range that meet a single criterion that you specify.

COUNTIF(*range*, *criteria*)

*range* - cell reference

*criteria* - text string, number, expression or a cell reference. Criteria are case insensitive.

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

### Category: Functions

Comments(14) Filed in category: Excel, Functions, Index

Comments(12) Filed in category: Excel, Functions

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.Comments(12) Filed in category: Excel, Functions

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]Comments(10) Filed in category: Excel, Functions

Comments(9) Filed in category: Excel, Functions

Learn how the MOD function works

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]Comments(8) Filed in category: Excel, Functions, Mod, Quotient

Comments(4) Filed in category: Excel, Functions, Transpose

Comments(4) Filed in category: Excel, Functions, Textjoin

MMULT function – Matrix multiplication

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]Comments(4) Filed in category: Excel, Functions, Mmult

### 5 Responses to “COUNTIF function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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