Table of Contents

  1. Introduction
  2. Example 1 - Count with single criterion
  3. Example 2 - Count cells larger/less than a criterion
  4. Example 3 - Count cells containing a text string
  5. Example 4 - Absolute and relative cell reference
  6. Example 5 - Array formula
  7. 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

countif - example1

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

=COUNTIF(C6:C13, "Lucy")

2 is returned in cell D5.

Example 2 - Count cells larger/less than a criterion

countif - example2

The following formula in cell D5 counts the number of cells within cell range C6:C13 that is larger than or equal to 500:

=COUNTIF(C6:C13, ">=")

Example 3 - Count cells containing a text string

countif - example3

The following formula in cell D5 counts the number of cells within cell range C6:C13 that contains the text string "apple":

=COUNTIF(c6:C13, "*apple*")

Example 4 - Absolute and relative cell reference

countif - example4

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

countif - example5

Array formula in cell range C6:C20:

=COUNTIF(B6:B20, B6:B20)

How to enter an array formula

  1.  Select cell range C6:C20
  2. Copy / Paste formula to formula bar
    formula bar
  3. Press and hold CTRL + SHIFT
  4. Press Enter
  5. 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

COUNTIF function.xlsx