ExcelFunctions.net

Excel Count Function

Related Functions:

COUNTA

COUNTBLANK

Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Count-Function

Excel COUNT & COUNTA Functions
As the Excel Count and the Excel Counta functions are often confused, a brief description of each of these functions is given below:
- The COUNT function returns the count of numeric values within a set of cells or values (includes numbers and dates)
- The COUNTA function returns the count of non-blank values within a set of cells or values (includes numbers, dates, text values, logical values and errors)

Basic Description

The Excel Count function returns the number of numeric values in a supplied set of cells or values. This count includes both numbers and dates.

The syntax of the function is :

COUNT( value1, [value2], ... )

Where the arguments, value1, [value2], etc, can be any values or references to cell ranges.

In Excel 2007 or 2010, you can enter up to 255 value arguments to the Excel Count function, each of which may consist of any number of cells or values. However, in Excel 2003, the function can only handle up to 30 arguments.


Which Values are Counted as Numeric Values?

Numbers and dates are always counted as numeric values by the Excel Count function. However, text representations and logical values are counted differently, depending on whether they are supplied as a value in a range of cells, or if they are supplied directly to the function.

The table below summarises which values are and which are not treated as numeric values by the Excel Count function:

Value Within a
Range of Cells		 Value Supplied
Directly to Function
Numbers ARE counted ARE counted
Dates ARE counted ARE counted
Logical Values NOT counted ARE counted
Text Representations of
Numbers & Dates		 NOT counted ARE counted
Other Text NOT counted NOT counted
Errors NOT counted NOT counted


Count Function Examples

Example 1 - Values Supplied from a Range of Worksheet Cells

The following spreadsheet shows several examples of the Excel Count function, with the values supplied to the function as one or more ranges of cells.

The spreadsheet on the left shows the formulas and the spreadsheet on the right shows the results.

 Formulas:
Examples of use of the Excel Count Function 		 Results:
Excel Count Function Results

Note, in the above example:

  • The numbers and the date, 01/01/2010 are counted by the function.
  • The text value "text", the logical value FALSE, and the error value #N/A are not counted by the function.
  • The example in cell C3 uses two ranges that intersect, and both ranges include the cell A1. In this case, Excel counts the cell A1 (which DOES contain a numeric value) twice - once for each range that it is contained in.

Example 2 - Values Supplied Directly to the Excel Count Function

The following spreadsheet shows several examples of the Excel Count function, with the values supplied directly to the function.

Again, the spreadsheet on the left shows the formulas and the spreadsheet on the right shows the results.

 Formulas:
Examples of use of the Excel Count Function 		 Results:
Excel Count Function Results

Note, in the above example:

  • The number 100 and the date 01/01/2010 are counted by the function.
  • The text representations of the number "100" & the date, "01/01/2010", and the logical value FALSE, are counted by the function.
  • The text string "text" and the error #N/A are not counted by the function.

Further examples of the Excel Count function can be found on the Microsoft Office website.