COUNTIFS Function in Excel

The COUNTIFS excel function counts the values of the supplied range based on one or multiple criteria (conditions). The supplied range can be single or multiple and adjacent or non-adjacent. Being a statistical function of Excel, the COUNTIFS supports the usage of comparison operators and wildcard characters.

For example, given the following table, the COUNTIFS excel function can count the total number of products with the name “B” for the east region. The formula “=COUNTIFS(A2:A13,“EAST”,B2:B13,“B”)” returns 2.

The COUNTIFS is different from the COUNTIF function in the sense that the latter counts the values in a single range based on one condition.

The syntax of the function is shown in the following image.

The function accepts the following arguments:

• Criteria_range1: This is the first range within which values are to be counted.
• Criteria1: This is the criteria (conditions) to be applied to “criteria_range1.” In other words, “criteria1” specifies the kind of cells which will be counted from “criteria_range1.”
• Criteria_range2: This is the second range within which values are to be counted.
• Criteria2: This is the criteria to be applied to “criteria_range2.” In other words, “criteria2” specifies the kind of cells which will be counted from “criteria_range2.”

The “criteria_range1” and “criteria1” are required arguments. The remaining range and criteria pairs are optional. In the COUNTIFs excel function, up to 127 range and criteria combinations can be specified.

Note 1: In case of multiple range and criteria pairs, the COUNTIFS counts only those cells that meet all the specified conditions.

Note 2: Every additional range should have the same number of rows and columns as the “criteria_range1.”

Let us consider a few examples of COUNTIFS formula of Excel.

Example #1 - Two Conditions

The following table shows the region-wise products of an organization. The regions are categorized according to the four directions north, south, east, and west. The product names are displayed with a single letter.

We want to count the total number of products with the name “B” for the east region.

The steps to count cells with the help of the COUNTIFS excel function are listed as follows:

Open the COUNTIFS formula.

Select column A (region) as the “criteria_range1.” Alternatively, you can select column B (product).

Select the “criteria1” for the range A2:A13. Since the cells matching the criterion “east” are to be counted, enter the same in the formula.

 Select column B (product) as the “criteria_range2.”

 Select the “criteria2” for the range B2:B13. Since the cells matching the criterion “B” are to be counted, enter the same in the formula.

Press the “Enter” key. The output is 2, as shown in the succeeding image. This implies that for the east region, there are two products with the name “B.”

Hence, the output matches two conditions, region “east” and product “B.”

Though product “B” is also present in cell B12, it has been ignored by the COUNTIFS formula. This is because this cell corresponds to the west region.

Example #2 - Three Conditions

Working on the data of example #1, we have added the names of the sales personnel (column C) to the dataset (shown in the succeeding image).

We want to count the total number of products “B” of the east region sold by Karan.

With the addition of a condition, we have to evaluate the data based on three criteria (region “east,” product “B,” and salesperson “Karan”).

In continuation with example #1 (preceding example), the additional steps are listed as follows:

Step 1: Enter the complete formula as written in example #1.

Step 2: Select column C (sales person) as the “criteria_range3.”

Step 3: Select the “criteria3” for the range C2:C13. Since the cells matching the criterion “Karan” are to be counted, enter the same in the formula.

Step 4: Press the “Enter” key. The output is 1, as shown in the succeeding image. This implies that for the east region, there is one product with the name “B” sold by the salesperson Karan.

Hence, the output matches three conditions, region “east,” product “B,” and salesperson “Karan.”

The total number of products corresponding to the name “B” and the region “east” is 2. However, with the addition of the criterion “Karan,” this count is reduced to 1.

Example #3 - Comparison (Logical) Operators

The logical operators are used in the COUTNIFS formula to structure the criteria. Let us consider an example of the same.

The following table shows the prices (in dollars) of the different products of an organization. The region in which each product is sold is also listed. The product names are displayed with a single letter.

We want to count the total number of products of the east region with the price greater than $20.

Step 1: Open the COUNTIFS formula.

Step 2: Select column C (region) as the “criteria_range1.”

Step 3: Select the “criteria1” for the range C2:C10. Since the cells matching the criterion “east” are to be counted, enter the same in the formula.

Step 4: Select column B (price) as the “criteria_range2.”

Step 5: Select the “criteria2” for the range B2:B10. Since the cells matching the criterion “>20” are to be counted, enter the same in the formula.

Note: The comparison operators in the “criteria” argument must be placed within double quotation marks.

Step 6: Press the “Enter” key. The output is 2, as shown in the following image. This implies that for the east region, there are two products with the price greater than $20.

Example #4 - Date Values and Comparison Operators

The following table shows the dates and the amounts for which invoices are issued to the buyer.

We want to count the number of invoices sent (or issued) between 20-June-2019 to 26-June-2019.

The value between the two given dates (cell D2 and D3) has to be counted in column E, as shown by a question mark in the following image.

Step 1: Open the COUNTIFS excel function.

Step 2: Select column A (invoice date) as the “criteria_range1.”

Step 3: Since we need to count the invoices sent after 20-June-2019, enter the greater than symbol (>) within the double quotation marks.

Step 4: Enter the “criteria1” for the range A2:A10. The cell D2 contains the beginning issue date. Since the cells matching the criterion “>”&D2 are to be counted, enter the same in the formula.

The ampersand ($) is added before the cell reference D2. The cell D2 can be selected rather than typing manually.

Step 5: Select the “criteria_range2” which is the same as “criteria_range1,” i.e., A2:A10.

Step 6: Since we need to count the invoices sent before 26-June-2019, enter the less than symbol (<) within the double quotation marks. This is shown in the succeeding image.

Step 7: Enter the “criteria2” for the range A2:A10. The cell D3 contains the ending issue date. Since the cells matching the criterion “<”&D3 are to be counted, enter the same in the formula.

Step 8: Press the “Enter” key. The output is 3, as shown in the following image. This implies that three invoices are sent between the dates 20-June-2019 and 26-June-2019.