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

Syntax:

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

Description:

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 allows you to specify more than just one range/criterion to be searched.

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:

Use numbers or text (like "42" or "bolts") to check if the cell contains a certain value.

Use conditions (like ">10" or "<=5") to check if the cell matches a certain condition.

Note: Criteria always have to be surrounded by double quotation marks (").

Unlike the COUNTIF function, COUNTIFS allows you to specify more than just one cell range and criterion to be checked. 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 by their last result as a fixed value.

Accordingly, if you 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 instead of a constant is entered for the search criterion. 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