### Count function examples

1999-08-14    Count    0    125

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.
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. Some examples uses an array formula, click here to read more about how to create those.

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 two AND-criterias (array formula) {=SUM((F5:F24="yes")*(G5:G24>10)*(A5:A24="North"))}
Counts cells with an OR-criteria (array formula) {=SUM((F4:F23="yes")+(F4:F23="no"))}