Count how often a value occurs within a range

To count how often a value occurs within a range we use the COUNTIF function.

Because this function count cells based on a given criteria, that fact will help us limit the count to our specified value.

If in the example below we want to count how often “John” appeared in the list, then we will use the following formula:

=COUNTIF(B1:B15;”John”)

This will give us a result of 7 as you can see.

Read more on How to use the COUNTIF function.

Applies to Excel 2003

Summary of Excel count functions

COUNT

Counts the number of cells that contain numbers within a given range. It can also accept a list of  arguments other than cell ranges and counts numbers within that list of arguments. Use COUNT to find out how many cells or arguments that have numeric values.

Syntax

COUNT(value1,value2,…)

Read more …


COUNTA

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Syntax

COUNTA(value1,value2,…)

Read more …


COUNTBLANK

Counts empty cells in a specified range of cells.

Syntax

COUNTBLANK(range)

Read more …


COUNTIF

Counts the number of cells within a range that meet the given criteria.

The criteria can be a number, text, expression or a cell reference.

Syntax

COUNTIF(range,criteria)

Read more …

Applies to Excel 2003


Count numbers greater than or less than a number

To count numbers greater than or less than a given number, you use the COUNTIF function.

As asn example  if you have a list of numbers in a range of cells B1:B20 and you want to know how many of those cells have a value greater than 100, then you use COUNTIF as follows:

COUNTIF(B1:B20,”>100″).

On the other hand if you want to know how many of those cells have a negative  value , then you use COUNTIF as follows:

COUNTIF(B1:B20,”<0″).

Read more about the COUNTIF function here.

Applies to Excel 2003

How to use the COUNTIF function

The COUNTIF function counts the number of cells within a given range that meet a given criteria.

Syntax
COUNTIF(range,criteria)

Range: The range of cells from which you want to count cells.

Criteria: The criteria upon which you want to base your count. It defines which cells will be counted.
The criteria can b a number, expression, cell reference, or a text.

Examples:

=COUNTIF(B15:B33;”>100″)
=COUNTIF(A1:A8;”ABC”)

The examples below show how to use COUNTIF with numeric values. A1 to A6 is our data range. In rows 7, 8 and 9 you have the text of the formula  in column A (for explanation only), the actual formula in column B and column C shows which cells are qualified to be counted.

COUNTIF and wildcards

You can use the wildcard characters question mark (?) and asterisk (*) in the COUNTIF criteria. A question mark matches any single character; an asterisk matches any sequence of characters.

Examples:

The examples below show how to use COUNTIF with wildcards. A1 to A11 is our data range. In rows 12, 13, 14, 15 and 16 you have the text of the formula  in column A (for explanation only),  the actual formula in column B, and column C shows which cells are qualified to be counted.

Note:

If you want to find an actual question mark or asterisk, type a tilde (~) before the character.