Documentation/Calc Functions/COUNTIF
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
COUNTIF
Category:
Statistical Analysis
Summary:
Calculates the number of cells in a range that satisfy a specified criterion.
The criterion passed to COUNTIF can utilize wildcards or regular expressions.
Syntax:
COUNTIF(Range; Criteria)
Returns:
Returns a non-negative integer that is the count of cells in the given range that satisfy the specified criterion.
Arguments:
Range argument
Range specifies the cells to be matched and counted. Range may be a cell range, the name of a named range, or the name of a database range.
Criteria argument
Criteria is the criterion for matching, or a cell containing that criterion. Criteria can take one of the following forms:
- A number, such as 34.5. Dates and logical values are treated as numbers.
- An expression, such as 2/3, SQRT($D$1), or DATE(2021; 11; 1).
- A text string, such as "golf" or "<>10".
COUNTIF looks for cells in Range that are equal to Criteria, unless Criteria is a text string that starts with a comparator (>, <, >=, <=, =, or <>). In the latter case COUNTIF compares the cells in Range with the remainder of the text string (interpreted as a number if possible and text otherwise). For example, the condition ">4.5" tests if the content of each cell is greater than the number 4.5, the condition "<dog" tests if the content of each cell comes alphabetically before the text "dog", and the condition "<>2023-03-23" tests if the content of each cell is not equal to the specified date.
Criteria supports the following specific behaviors:
- The string "=" matches empty cells. For example the formula
=COUNTIF(B1:B10; "=")returns the count of empty cells in the range B1:B10. Note that "=0" does not match empty cells. - The string "<>" matches non-empty cells. For example the formula
=COUNTIF(C1:C10; "<>")returns the count of non-empty cells in the range C1:C10. - If the value after the <> comparator is not empty, then Criteria matches any cell content except that value, including empty cells.
Additional details:
Details specific to COUNTIF function
- COUNTIF allows only one matching criterion. If you need to count cells based on multiple criteria, use the COUNTIFS function.
- The default matching performed by COUNTIF is case-insensitive. However, a case-sensitive match can be carried out when using a regular expression by including a mode modifier "(?-i)" within the regular expression, as demonstrated by one of the examples below.
- The behavior of COUNTIF is affected by several settings available on the ▸ ▸ ▸ dialog ( ▸ ▸ ▸ on macOS).
- If the checkbox is ticked for Search criteria = and <> must apply to whole cells, then the condition "red" will match only "red"; if unticked it will match "red", "Fred", and "red herring".
- If the checkbox is ticked for Enable wildcards in formulas, the condition will match using wildcards – so for example "b?g" will match "bag", "beg", "big", "bog", and "bug".
- If the checkbox is ticked for Enable regular expressions in formulas, the condition will match using regular expressions – so for example "r.d" will match "red", "rid", and "rod", while "red.*" will match "red", "redraw", and "redden".
- The setting of the Case sensitive checkbox has no impact on the operation of COUNTIF.
General information about Calc's regular expressions
 |
Note:
|
- A regular expression is a string of characters defining a pattern of text that is to be matched. More detailed, general background information can be found on Wikipedia’s Regular expression page.
- Regular expressions are widely used in many domains and there are multiple regular expression processors available. Calc utilises the open source Regular Expressions package from the International Components for Unicode (ICU). See their Regular Expressions documentation for further details, including a full definition of the syntax for ICU Regular Expressions.
- In addition, the LibreOffice Help system provides a high-level list of regular expressions.
- Calc’s regular expression engine supports numbered capture groups, which allow sub-ranges within a match to be identified and used within replacement text. Parentheses are used to group components of a regular expression together and create a numbered capture group. To insert a capture group into a replacement text, use the "$n" form, where n is the number of the capture group.
General information about Calc's wildcards
Wildcards are special characters that can be used in search strings passed as arguments to some Calc functions; they can also be used to define search criteria in the Find & Replace dialog. The use of wildcards enables the definition of more advanced search parameters with a single search string.
Calc supports either wildcards or regular expressions as arguments, depending on the current application settings. By default, wildcards are supported instead of regular expressions.
To make sure wildcards are supported, go to ▸ ▸ ▸ and check whether the option Enable wildcards in formulas is selected. Note that you can use this dialog to switch to regular expressions by choosing Enable regular expressions in formulas or choose to support neither wildcards nor regular expressions.
The following table identifies the wildcards that Calc supports.
| Wildcard | Description |
|---|---|
| ? (question mark) | Matches any single character. For example, the search string "b?g" matches "bag" and "beg" but will not match "boog" or "mug". Note that it will not match "bg" either, since "?" must match exactly one character. The "?" wildcard does not correspond to a zero-character match. |
| * (asterisk) | Matches any sequence of characters, including an empty string. For example, the search string "*cast" will match "cast", "forecast", and “outcast”, but will not match "forecaster" using default Calc settings. If the option Search criteria = and <> must apply to whole cells is disabled in ▸ ▸ ▸ , then "forecaster" will be a match using the "*cast" search string.
|
| ~ (tilde) | Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character. For example, the search string "why~?" matches "why?" but will not match "whys" nor "why~s". |
Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".
These wildcards are supported in both Calc and Microsoft Excel. Therefore, if interoperability between the two applications is needed, choose to work with wildcards instead of regular expressions. Conversely, if interoperability is not necessary, consider using regular expressions for more powerful search capabilities.
Examples:
Basic examples
| Formula | Description | Returns |
|---|---|---|
| =COUNTIF(A1:A9; "<0") where cells A1 to A9 contain the numbers -1, 2, -3, 4, -5, 6, -7, 8, and -9 respectively. | Here the function counts the negative numbers in the cell range A1:A9. | 5 |
| =COUNTIF(A1:A9; F1) where cells A1 to A9 contain the numbers -1, 2, -3, 4, -5, 6, -7, 8, and -9 respectively and cell F1 contains the string ">=0" (entered without typing the double quotes). | Here the function sums the non-negative numbers in the cell range A1:A9. | 4 |
| =COUNTIF(B2:B4; "<"&F2) where cells B2 to B4 contain the numbers 7, 9, and 11 respectively. Cell F2 contains the number 10. | Here the function determines that the values in cells B2 and B3 are less than 10. | 2 |
| =COUNTIF(D1:D9; "apples") where cells D1, D4, D5, D6, and D9 contain the string "apples"; cells D2, D3, D7, and D8 contain the text "pears". | Here the function counts the cells containing the string "apples". | 5 |
=COUNTIF(A1:A5; "<>") where cell A1 contains the date 2023-03-22, cell A2 contains the logical value TRUE, cell A3 contains the formula =3/0 (error #DIV/0!), cell A4 contains the string "hello world", and cell A5 is empty. |
Here the functions counts the number of non-empty cells. | 4 |
Other examples
The examples in this subsection are based on a small database of sales data for sports equipment, with the data organized as in the following table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Sales Value | Category | Region | Employee |
| 2 | 2021-10-02 | $1,508 | Golf | East | Hans |
| 3 | 2021-10-02 | $410 | Tennis | North | Kurt |
| 4 | 2021-10-02 | $2,340 | Sailing | South | Ute |
| 5 | 2021-10-03 | $4,872 | Tennis | East | Brigitte |
| 6 | 2021-10-06 | $3,821 | Tennis | South | Fritz |
| 7 | 2021-10-06 | $2,623 | Tennis | East | Fritz |
| 8 | 2021-10-07 | $3,739 | Golf | South | Fritz |
| 9 | 2021-10-08 | $4,195 | Golf | West | Ute |
| 10 | 2021-10-10 | $2,023 | Golf | East | Hans |
| Formula | Description | Returns |
|---|---|---|
| =COUNTIF(B2:B10; ">=4000") | Here the function counts the entries in the Sales Value data that are greater than or equal to $4,000. | 2 |
| =COUNTIF(E2:E10; "ute") | Here the function counts the entries for Ute in the Employee data. | 2 |
| =COUNTIF(CategoryData; "golf") where the named range CategoryData has been created to cover the cell range C2:C10. | Here the function counts the entries for Golf in the Category data. | 4 |
| =COUNTIF(D2:D10; F1) where cell F1 contains the text ">=south" (entered without typing the double quotes). | Here the function counts the entries for South and West in the Region data. | 4 |
| =COUNTIF(A2:A10; DATE(2021; 10; 2)) | Here the function counts the entries for 2021-10-02 in the Date column. | 3 |
| =COUNTIF(A2:A10; ">="&DATE(2021; 10; 7)) | Here the function counts the entries dated on or after 2021-10-07 in the Date column. | 3 |
| =COUNTIF(D2:D10; "????") | This example will only work as described here if wildcards are enabled. Here the function counts the four-character entries in the Region data (East and West). | 5 |
| =COUNTIF(E2:E10; "^f.*") | This example will only work as described here if regular expressions are enabled. Here the function counts the entries in the Employee data that start with the letter "f" or "F" (Fritz). | 3 |
| =COUNTIF(E2:E10; "(?-i)ute") | This example will only work as described here if regular expressions are enabled. The "(?-i)" mode modifier within the regular expression changes to a case-sensitive match and so no entries are found in the Employee data. Contrast this with the second example in this table. | 0 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
COUNTIF