The COUNTIFS function, how to use the cell count function according to multiple events in Excel

The COUNTIFS function in Excel is used to count cells but satisfy the given conditions. The COUNTIFS function is one of the more commonly used Excel functions in Excel, which is the advanced function of the COUNTIF function that only counts cells with a given condition. When done with the COUNTIFS function, users can easily find the result cell that satisfies the conditions specified in the request. Conditions can be numbers, dates, text or cells containing data. The following article will guide you how to use the COUNTIFS function in Excel.

1. How to compare data on 2 Excel columns
2. Using the DSUM function to calculate the sum with complex conditions in Excel
3. How to use the SUMIF function for conditional summing in Excel
4. VLOOKUP function to use and specific examples

Instructions for using COUNTIFS function in Excel

COUNTIFS function syntax is = COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], .) .

Inside:

1. Criteria_range1 is the first selection that requires statistics and required values.
2. Criteria1 is a condition that applies to criteria_range1 selection, the required value can be cell, expression, text.
3. [criteria_range2, criteria2] are selections and additional conditions, which allow up to 127 selection pairs and conditions.

Note when using the COUNTIFS function:

1. Additional selections need to have the same number of rows and columns with the criteria_range1 area, which can be separated.
2. If the selection condition refers to an empty cell, the COUNTIFS function automatically evaluates to 0.
3. Can the characters be used? To replace certain characters, the * symbol replaces an entire string. If you need to find the sign? or a true *, then type ~ in front of that character.

1. Data sheet No. 1

We have the data table below to make some requests for the table.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 1

Example 1: Calculate the number of male employees with 25 working days.

In the field to enter the result, enter the formula = COUNTIFS (C2: C7, "Male", D2: D7,25) and press Enter.

Inside:

1. C2: C7 is the area of ​​counting 1 required for gender of employees.
2. Male is the condition of the count area 1.
3. C2: C7 is the counting area 2 with the Employee's Day.
4. 25 is the condition of the count area 2.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 2

As a result, we have 2 employees, Nam who has 25 man days.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 3

Example 2: Calculate the number of male employees whose holidays are 0.

In the formula input box, enter = COUNTIFS (C2: C7, "Male", E2: E7,0) and press Enter.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 4

Results show 1 male employee has 0 holidays.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 5

Example 3: Calculating the number of male employees with a 25-day workday and having less than or equal to 2 days of leave.

We enter the formula = COUNTIFS (C2: C7, "Male", D2: D7,25, E2: E7, "<= 2") and press Enter.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 6

The result is that 2 male employees who meet the requirements have a holiday of less than or equal to 2 days.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 7

2. Data sheet no.2

Example 1: Calculate the total of Taiwanese goods with a selling price under 200,000 VND.

In the input box, enter the formula = COUNTIFS (B2: B7, "* Taiwan", C2: C7, "<200,000") and press Enter.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 8

The result is 2 Taiwanese products in accordance with the requirements.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 9

Example 2: Calculate the total product of Taiwanese products with a unit price of between VND 100,000 and VND 150,000.

At the input box, enter the formula = COUNTIFS (B2: B7, "* Taiwan", C2: C7, "> 100000", C2: C7, "<150000") and then press Enter.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 10

As a result, two Taiwanese products will be priced between VND 100,000 and VND 150,000.

The COUNTIFS function, how to use the cell count function according to multiple events in Excel Picture 11

Above are 2 data tables with usage for COUNTIFS function in Excel. The user must write the condition that comes with the conditional data area so that the COUNTIFS function determines correctly.

I wish you all success!