Table of Contents
Statistical Functions
COUNT
- Counts how many numbers are in the list of arguments.Text entries are ignored.
Syntax
COUNT(Value1; Value2; …; Value30) - Value1; Value2, …, Value30 are 1 to 30 values or ranges representing the values to be counted.
The entries 2, 4, 6 and eight in the Value 1-4 fields are to be counted.
=COUNT(2;4;6;”eight”) = 3. The count of numbers is therefore 3.
COUNTA
- Counts how many values are in the list of arguments. Text entries are also counted, even when they contain an empty string of length 0. If an argument is an array or reference, empty cells within the array or reference are ignored.
Syntax
COUNTA(Value1; Value2; …; Value30)
Value1; Value2, …, Value30 are 1 to 30 arguments representing the values to be counted.
The entries 2, 4, 6 and eight in the Value 1-4 fields are to be counted.
=COUNTA(2;4;6;”eight”) = 4. The count of values is therefore 4.
COUNTBLANK
- Returns the number of empty cells.
Syntax
COUNTBLANK(Range) - Returns the number of empty cells in the cell range Ra
=COUNTBLANK(A1:B2) returns 4 if cells A1, A2, B1, and B2 are all empty.
COUNTIF
- Returns the number of cells that meet with certain criteria within a cell range.
Syntax
COUNTIF(Range; Criteria) - Range is the range to which the criteria are to be applied.
- Criteria indicates the criteria in the form of a number, an expression or a character string. These criteria determine which cells are counted. If regular expressions are enabled in calculation options you may also enter a search text in the form of a regular expression, e.g. b.* for all cells that begin with b. If wildcards are enabled in calculation options you may enter a search text with wildcards, e.g. b* for all cells that begin with b. You may also indicate a cell address that contains the search criterion. If you search for literal text, enclose the text in double quotes.
A1:A10 is a cell range containing the numbers 2000 to 2009. Cell B1 contains the number 2006. In cell B2, you enter a formula:
=COUNTIF(A1:A10;2006) – this returns 1.
=COUNTIF(A1:A10;B1) – this returns 1.
=COUNTIF(A1:A10;”>=2006″) – this returns 4.
=COUNTIF(A1:A10;”<“&B1) – when B1 contains 2006, this returns 6.
=COUNTIF(A1:A10;C2) where cell C2 contains the text >2006 counts the number of cells in the range A1:A10 which are >2006.
To count only negative numbers: =COUNTIF(A1:A10;”<0″)