Recent Addins ADO Application Arrays Charts Commandbars Count Dates Dialogs Events File access Files & Folders Functions Games Help Import & Export Lookup Off topic Other Outlook PivotTables Power Query Printing Problem solving Read me Searching Sorting Sound Strings Sum Time VBA programming VBE Workbooks Worksheets

Count function examples

 1999-08-14    Count    0    652

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. 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"))}