Documentation/Calc Functions/COUNTIF

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

COUNTIF

Category:

Statistical Analysis

Summary:

Returns the number of cells that meet with certain criteria within a cell range.

Syntax:

COUNTIF(Range; Criteria)

Returns:

Returns a non-negative integer which is the number of cells in Range that satisfy the given Criteria.

Arguments:

Range is a reference to the range of cells to which the criteria are to be applied.

Criteria is a criterion or a reference to the cell containing that criterion which can be in the form of a number, an expression, or a character string. These criteria determine which cells are counted.

  • If the Criteria is not a satisfied or valid criterion then the function returns zero.

Additional details:

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example, to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools > Options > LibreOffice Calc > Calculate.

Gnome-dialog-warning.svg

Warning:
When using functions where one or more arguments are search criteria strings that represent a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Examples:

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:

Formula Description Returns
=COUNTIF(A1:A10;2006) Counts number of cells containing 2006 in them 1
=COUNTIF(A1:A10;B1) Counts number of cells containing content of B1 in them 1
=COUNTIF(A1:A10;">=2006") Counts number of cells containing content >=2006 in them 4
=COUNTIF(A1:A10;"<"&B1) Counts number of cells containing content less than the content of B1 in them 6
=COUNTIF(A1:A10;"<0") To count only negative numbers 0

Related LibreOffice functions:

COUNT

COUNTA

COUNTBLANK

COUNTIFS

SUMIF

ODF standard:

Section 6.13.9, part 2

Equivalent Excel functions:

COUNTIF