Formulas and functions > Functions from A to Z > COUNTIFS (count if conditions are true)

Syntax:

COUNTIFS(Range1, Criterion1 [, Range2, Criterion2 ...])

Description:

This applies criteria to the cells in the specified cell ranges and counts how often all of these criteria are fulfilled.

This function is similar to the COUNTIF function, but it allows you to specify multiple search ranges (including one criterion for each range).

Range1 is the first cell range to be checked for the specified criterion. Please note that all of the cell ranges specified must have the same dimensions.

Criterion1 is the condition that the values in Range1 have to fulfill to be included:

On the one hand, you can simply specify a value here (for example, "42" or "screws"). In this case, the condition is considered to be fulfilled if the cell contains this value.

Furthermore, you can also use conditions like ">10" or "<=5". Here, the condition is considered to be fulfilled if the cell content matches this condition.

Note: Criteria always have to be enclosed within double quotation marks (").

Unlike the COUNTIF function, COUNTIFS allows you to specify multiple search ranges (including one criterion for each range). If you do so, the function counts only occurrences where all criteria are fulfilled. See examples below.

Compatibility notes:

This function is not supported by the .xls file format (used in Microsoft Excel 2003 and earlier). If you save a document in this format, all calculations using this function will be replaced with their current result as a fixed value.

If you want to use this function, you should not save your document in the "Microsoft Excel 97-2003 (.xls)" file format, but choose one of the following formats instead:

▪"PlanMaker document (.pmdx or .pmd)"

▪or: "Microsoft Excel 2007-2021 (.xlsx)"

Example:

COUNTIFS(C1:C10, ">1", C1:C10, "<2")

This formula counts how many cells in the cell range C1:C10 are greater than 1 and less than 2.

COUNTIFS(A1:D4, "tuna", G10:K14, ">500")

This formula counts how often the following conditions are met: The cell in A1:D4 contains "tuna" and the corresponding cell in G10:K14 contains a value greater than 500.

Tip: A modification of the formula is required if a cell reference is entered for the search criterion instead of a constant. Here's an example:

The cells A1:A5 contain the values 1, 2, 3, 4, 5. In addition, cell C1 contains the value 2.
You now want to find out how many values in the range A1:A5 are greater than the value in cell C1.

Then the formula is COUNTIF(A1:A5, ">"&C1) and returns 3