Excel Count Function

Excel COUNT & COUNTA Functions

The Excel Count and the Excel Counta functions both count the number of values within a supplied set of values or range of cells.

The two functions differ in the following ways:

The Count function returns the count of numeric values (includes numbers and dates);
The Counta function returns the count of all non-blank values (includes numbers, dates, text values, logical values and errors).

Related Functions:

COUNTA
COUNTBLANK

Function Description

The Excel Count function returns the count 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, arrays of values, or references to cell ranges.

In recent versions of Excel (2007 and later), you can enter up to 255 value arguments to the Excel Count function, each of which may be single values or arrays of cells or values. However, in Excel 2003, the function can only handle up to 30 value 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

Column C of the following spreadsheet shows three examples of the Count function, used to return the number of numeric values in one or more supplied ranges of cells.

Formulas:

	A	B	C
1	5	0	=COUNT( A1:A5 )
2	text		=COUNT( A1:A5, B1 )
3	FALSE		=COUNT( A1:B5 )
4	01/01/2015
5	#N/A	10

Results:

	A	B	C
1	5	0	2
2	text		3
3	FALSE		4
4	01/01/2015
5	#N/A	10

Note that, in the above example:

The numbers and the date 01/01/2015 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 empty cells are not counted by the function.

Example 2 - Values Supplied Directly to the Excel Count Function

In the following spreadsheet, the Excel Count function is used to count the number of numeric values in sets of values supplied directly to the function.

Formulas:

	A
1	=COUNT( 100, DATE(2015,1,1) )
2	=COUNT( "100", "01/01/2015", FALSE )
3	=COUNT( "text", #N/A )

Results:

	A
1	2
2	3
3	0

Note, in the above example:

The number 100 and the date 01/01/2015 are counted by the function.
The text representations of the number "100" & the date, "01/01/2015", 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.

For further examples of the Excel Count function, see the Microsoft Office website.

Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions