Functions for counting

In Excel you can count the content of cells that meet different criteria in several different ways. These are some of the functions that can be used:

COUNT
Counts the number of cells in a range that contains numeric values.
COUNTA
Counts the number of nonblank cells in a range.
COUNTBLANK
Counts the number of blank cells in a range.
COUNTIF
Counts the number of cells in a range that meet a given criteria.
An array formula can also be useful in cases that none of the above functions are suitable.
DCOUNT
This database-function requires values stored in a table with columnheadings and a separate criteria range to describe which values are to be counted.
More information about database functions.

The following examples demonstrate the COUNT functions listed above. The range address may be replaced with a defined name (e.g. MyRange). In this example a semicolon (;) is used as the list separator, repace with a comma (,) if necessary.

Description Formula

Counts the values in the range E4:E23 =COUNT(E4:E23)

Counts all non-blank cells in the range E4:E23 =COUNTA(E4:E23)

Counts blank cells in the range E4:E23 =COUNTBLANK(E2:E23)

Counts all values greater than 0 =COUNTIF(E4:E23;">0")

Counts all values less than 0 =COUNTIF(E4:E23;"<0")

Counts all cells containing the text yes =COUNTIF(F4:F23;"yes")

Counts all cells containing text starting with k =COUNTIF(F4:F23;"k*")

Counts all cells containing text with the letter a =COUNTIF(F4:F23;"*a*")

Counts all cells containing the text yes or no =COUNTIF(F4:F23;"yes")+COUNTIF(F4:F23;"no")

Same as above with an array formula {=SUM((F4:F23="yes")+(F4:F23="no"))}

Counts all cells containing 3 characters =COUNTIF(F4:F23;"???")

Counts all cells with values between 30 and 80 =COUNTIF(E4:E23;">=30")-COUNTIF(E4:E23;">80")

Counts all unique numbers in the range =SUM(IF(FREQUENCY(E4:E23;E4:E23)>0;1;0))

Counts all odd numbers in the range (array formula) {=SUM(MOD(G4:G23;2))}

Counts all even numbers in the range (array formula) {=SUM(IF(MOD(G4:G23;2)=0;1;0))}

Counts cells with an AND-criteria (array formula) {=SUM(IF((F4:F23="yes")*(G4:G23>10);1;0))}

Counts cells with an AND-criteria (array formula) {=SUM((F5:F24="yes")*(G5:G24>10))}

Counts cells with an OR-criteria (array formula) {=SUM((F4:F23="yes")+(F4:F23="no"))}

Document last updated 1999-08-14 12:44:24