Documentation/Calc Functions/COUNTIFS

    From The Document Foundation Wiki

    Function name:

    COUNTIFS

    Category:

    Statistical Analysis

    Summary:

    Counts the number of times that multiple criteria are met across multiple cell ranges. The primary difference between COUNTIFS and Calc's COUNTIF function is that the latter is limited to evaluating only one criterion.

    The criteria passed to COUNTIFS can utilize wildcards or regular expressions.

    Syntax:

    COUNTIFS(Range 1; Criteria 1 [; Range 2; Criteria 2 [; ... ;[Range 127; Criteria 127]])

    Returns:

    Returns a non-negative integer that is the count of matches found. The count is incremented only when the corresponding cells across all supplied ranges satisfy their associated criteria.

    Arguments:

    Range Arguments

    Range 1 specifies the cells to be matched against Criteria 1. Range 1 may be a cell range, the name of a named range, or the name of a database range.

    Range 2 to Range 127 each take one of the forms listed for Range 1 but are matched against Criteria 2 to Criteria 127 respectively. Range 1 to Range 127 should each occupy the same number of rows and the same number of columns.

    Criteria arguments

    Criteria 1 is the criterion for matching against the cells in Range 1, or a cell containing that criterion. Criteria 1 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".

    COUNTIFS looks for cells in Range 1 that are equal to Criteria 1, unless Criteria 1 is a text string that starts with a comparator (>, <, >=, <=, =, or <>). In the latter case COUNTIFS compares the cells in Range 1 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-30" tests if the content of each cell is not equal to the specified date.

    Criteria 1 supports the following specific behaviors:

    • The string "=" matches empty cells. For example the formula =COUNTIFS(A1:A10; "=") returns the number of empty cells in the range A1:A10. Note that "=0" does not match empty cells.
    • The string "<>" matches non-empty cells. For example the formula =COUNTIFS(A1:A10; "<>") returns the non-empty cells in the range A1:A10.
    • If the value after the <> comparator is not empty, then Criteria 1 matches any cell content except that value, including empty cells.

    Criteria 2, ..., Criteria 127 have the same meaning as Criteria 1 but are matched against Range 2 to Range 127 respectively.

    Error conditions

    • If any cell range passed as an argument contains a reference concatenation operator (~), then COUNTIFS reports an invalid argument error (Err:502).
    • If Range 1 ... Range 127 are not of the same dimensions, then COUNTIFS reports an invalid argument error (Err:502).
    • If the Range n and Criterion n arguments are not correctly paired, then COUNTIFS reports a variable missing error (Err:511).

    Additional details:

    Details specific to COUNTIFS function

    • The default matching performed by COUNTIFS 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 COUNTIFS is affected by several settings available on the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate dialog (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS).
      1. 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".
      2. 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".
      3. 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".
      4. The setting of the Case sensitive checkbox has no impact on the operation of COUNTIFS.

    General information about Calc's regular expressions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing functions that manipulate regular expressions.

    • 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 Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate and check if 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.

    Calc wildcards
    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" as well, 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 Tools > Options > LibreOffice Calc > Calculate, 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:

    Stationery sales examples

    Consider the following table showing sales and revenue information for a small stationery supplier. The string "N/A" refers to products that were not available for supply during the period covered by the data.

    A B C
    1 Product Sales Revenue
    2 Pencil 20 $65
    3 Pen 35 $85
    4 Notebook 20 $190
    5 Book 17 $180
    6 Pencil case N/A N/A
    Formula Description Returns
    =COUNTIFS(B2:B6; ">=20") Here the function counts the Sales data entries that are greater than or equal to 20. 3
    =COUNTIFS(B2:B6; ">=20"; C2:C6; ">70") Here the function counts entries with a value greater than or equal to 20 in the Sales data that also have a value greater than $70 in the Revenue data. 2
    =COUNTIFS(B2:B6; ">"&MIN(B2:B6); C2:C6; "<"&MAX(C2:C6)) Here the function counts entries for which the Sales value is greater than the minimum of all Sales data and the Revenue value is less that the maximum of all Revenue data. 2
    =COUNTIFS(A2:A6; "pen.*"; B2:B6; "<"&MAX(B2:B6)) This example will only work as described here if regular expressions are enabled. Here the function counts entries with Product data that begins with the characters "pen" and have a value in the Sales data that is not the maximum. 2
    =COUNTIFS(A2:A6; E2&".*"; B2:B6; "<"&MAX(B2:B6)) where cell E2 contains the string "pen" (entered without typing the double quotes). This example will only work as described here if regular expressions are enabled. If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the COUNTIFS function. Here the link to the cell is substituted with its content, giving the same result as the previous example. 2

    Sporting equipment sales 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
    =COUNTIFS(B2:B10; ">=4000") Here the function counts the entries in the Sales Value data that are greater than or equal to $4,000. This formula is equivalent to =COUNTIF(B2:B10; ">=4000"). 2
    =COUNTIFS(E2:E10; "ute") Here the function counts the entries for Ute in the Employee data. This formula is equivalent to =COUNTIF(E2:E10; "ute"). 2
    =COUNTIFS(CategoryData; "golf"; D2:D10; "east") where the named range CategoryData has been created to cover the cell range C2:C10. Here the function counts the entries that have Golf in the Category data and East in the Region data. 2
    =COUNTIFS(D2:D10; F1; E2:E10; F2) where cells F1 and F2 contain the text strings ">=south" and ">=hans" respectively (both entered without typing the double quotes). Here the function counts the entries for South and West in the Region data that also have Hans, Kurt, or Ute in the Employee data. 2
    =COUNTIFS(A2:A10; DATE(2021; 10; 2); C2:C10; "tennis") Here the function counts the entries for 2021-10-02 in the Date column that also have Tennis in the Category data. 1
    =COUNTIFS(A2:A10; ">="&DATE(2021; 10; 6); E2:E10; "<>"&E8) Here the function counts the entries dated on or after 2021-10-06 in the Date column that do not have Fritz in the Employee data. 2
    =COUNTIFS(C2:C10; "tennis"; D2:D10; "east"; E2:E10; "fritz") Here the function counts the entries that have Tennis in the Category data, East in the Region data, and Fritz in the Employee data. 1
    =COUNTIFS(D2:D10; "????"; E2:E10; "*e") 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) that also have an entry in the Employee data that ends with the letter "e" or "E" (Brigitte and Ute). 2
    =COUNTIFS(C2:C10; "^t.*"; D2:D10; ".*h") This example will only work as described here if regular expressions are enabled. Here the function counts the entries in the Category data that start with the letter "t" or "T" (Tennis) that also have an entry in the Region data that ends with the letter "h" or "H" (North and South). 2
    =COUNTIFS(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

    Additional examples

    For more examples, download and view this Calc spreadsheet.

    Related LibreOffice functions:

    AVERAGEIFS

    COUNT

    COUNTA

    COUNTBLANK

    COUNTIF

    MAXIFS

    MINIFS

    SUMIFS

    ODF standard:

    Section 6.13.10, part 2

    Related (or similar) Excel functions:

    COUNTIFS