Use CountIf, one of the statistical functions, to count the number of rows that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

Syntax

CountIf(range, value)

For example:

  • =CountIf([Table]![1][1]:[1][5], "apples")
  • =CountIf([Table]![1][1]:[1][5], [Fruit])

Argument Name

Description

range (required)

The group of rows you want to count. Range can contain numbers, arrays, a named range, or references that contain numbers. Blank and text values are ignored.

value (required)

A number, expression, field reference, or text string that determines which cells will be counted.

For example, you can use a number like 32, a comparison like ">32", a field like [Fruit], or a word like "apples".

Examples

Set up a table with the following values:

Data

Data

apples

32

oranges

54

peaches

75

apples

86

Formula

Description

=CountIf([Table]![1][1]:[1][4], "apples")

Counts the number of rows with apples in column 1. The result is 2.

=CountIf([Table]![1][1]:[1][4], [Table]![1][3])

Counts the number of rows with peaches (using criterion in column 1 row 3) in column 1. The result is 1.

=CountIf([Table]![1][1]:[1][4], [Table]![1][2]) +CountIf([Table]![1][1]:[1][4], [Table]![1][1])

Counts the number with oranges (using criterion in column 1 row 2) and apples (using criterion in column 1 row 1) in column 1. The result is 3. This formula uses two CountIf expressions to specify multiple criteria, one criteria per expression.

=CountIf([Table]![1][1]:[1][4], "*")

Counts the number of rows containing any text in column 2. The asterisk (*) is used as the wildcard character to match any character. The result is 4.