COUNTIFS Function (multiple criteria)

COUNTIFS Function

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

=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.

COUNTIFS function

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")

COUNTIFS function

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).

COUNTIFS function

 

 

Related Trainings

Highest Paid Celebrities

Highest Paid Celebrities

Look at the World's Highest-Paid Celebrities. Gain more insights using rankings, basic statististics and conditional sta...


Richest Business People

Richest Business People

Look at the some of richest people in the world. A table of these people age, earnings, the company owned by those compa...


World Internet Users Statistics

World Internet Users Statistics

Create meaningful insights from internet users of top 25 countries between 2013-2015.