The COUNTIFS() function is an extended version of the COUNTIF() function which is used to conditionally count items/ cells based on certain criteria.

The Syntax of this COUNTIFS() function can be explained as follows:

=COUNTIFS(criteria_range1, criteria 1, criteria_range2, criteria 2…..)

Here, ‘criteria_range1’ refers to the range of the first criteria and ‘criteria 1’ refers to the first condition that specifies which items are to be counted.

Similarly, multiple conditions can be added with criteria_range2, criteria 2…..

Note: Criteria can be a number, expression or a text string

Example

The function can be best explained by an example.

Consider the following data set that shows car stock availability based on the car manufacturer. We want to count the number of available car manufacturers which have more than 50 cars in the stock. This can be done using two conditional arguments.

The two conditions are as follows:

• Car Manufacturer:                AUDI
• Stock availability:                   More than 50 cars

Those conditions are arranged in the following syntax.

=COUNTIFS(A2:A16,A2,B2:B16,">50")

As you see, A2:A16 shows the car manufacturers and B2:B16 is the available car stock. Criteria 1 is A2 (i.e. AUDI) and the criteria 2 is “>50”.

Similar syntax can be used for criteria_range3, criteria 3….. if more data is available.

Note that if the criteria is a text, logic, number or string, then it should be included in double quotations.

In the following snapshot, it shows the cells that satisfy all two conditions are counted for the answer (cell B18).