In the process of working on Excel, you have to be familiar with a lot of different math functions. There are functions you rarely use, but there are functions you must use often, such as the COUNTIFS function. Many of you will be quite unfamiliar with this function, but it is really very effective for you to use in counting operations containing many conditions. Let's learn about the COUNTIFS function with Web888.

In previous articles, Tech12h has shown you some functions used in Excel such as the SUM function to calculate the sum, the SUMIF function to calculate the conditional sum, the IF conditional function or the AVERAGE function to calculate the average value … and a function. more functions. And today, Tech12 will continue to bring you another new function that is the COUNTIFS function – a counting function containing many conditions.

The content of the article consists of 2 parts:

• Introduction to the COUNTIFS . function
• An example of the COUNTIFS . function

1. Introduction to the COUNTIFS . function

The COUNTIFS function is used to count the number of cells that satisfy or or more different conditions.

In essence, the COUNTIFS function is an enhanced function of the COUNTIF function . Accordingly, it can overcome some basic limitations that the COUNTIF function has not yet overcome. Specifically, it can perform a count containing many different conditions quickly instead of the COUNTIF function, which only counts the operation containing one condition.

The function syntax is as follows:

=COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)

In there:

• Criteria_range1: count range 1 (required)
• Criteria1: Counting condition 1 (required)
• Criteria_range2: Counting area 2
• Criteria2: Counting condition 2…..

Example: We have the following table:

Ask you to count the number of employees whose workdays are 23 days.

We have the following formula:

=COUNTIFS(D2:D6,"employee",E2:E6,23) the result is 2

To understand better, more specifically, please go to the example below.

2. Example COUNTIFS . function

We have the following data table:

Request:

• Count the number of female friends who have 23 working days this month?
• Count the number of men with 0 days off?
• Count the number of female friends who have 23 working days and vacation days less than or equal to 1 day?

To solve the above problems, we proceed as follows:

First, count the number of friends who have 23 working days this month.

First, you need to define the count ranges and conditions:

• Counting area 1: Gender column C2:C9
• Condition 1: Female
• Counting area 2: Date column E2:E9
• Condition 2: 23 working days

From the above analysis, inserting the syntax we get:

=COUNTIFS(C2:C9,"female",E2:E9,23) the result is 4

Second, count the number of men with 0 days off.

Similarly, we define the counting area and the condition:

• Counting area 1: Gender column C2:C9
• Condition 1: Male
• Counting area 2: Holiday column F2:F9
• Condition 2: 0 days off

Then we have the following calculation formula:

=COUNTIFS(C2:C9,"nam",F2:F9,0) the result is 0.

Finally, count the number of female friends who have 23 working days and have 1 day off or less.

Unlike the two questions above, this one contains three conditions.

• Counting area 1: Gender column C2:C9
• Condition 1: Female
• Counting area 2: Date column E2:E9
• Condition 2: 23 days
• Counting area 3: Holiday column F2:F9
• Condition 3: 0 days

Just plug in the syntax, we get the following formula:

=COUNTIFS(C2:C9,"female",E2:E9,23,F2:F9,0) the result is 2.

Thus, I have guided you to familiarize yourself with and use the COUNTIFS function. It will not be too difficult if you grasp and understand its calculation formula. If you still can't do it, you can comment below for more specific instructions. In addition, for more information, you can refer to the article on basic functions on Excel . Surely, it will give you a lot of good things about this tool.

If you find the article useful, please Like and Share to let more people know. Thank you!