ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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      Printerfriendly version

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address