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.
- How to compare data on 2 Excel columns
- Using the DSUM function to calculate the sum with complex conditions in Excel
- How to use the SUMIF function for conditional summing in Excel
- 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:
- Criteria_range1 is the first selection that requires statistics and required values.
- Criteria1 is a condition that applies to criteria_range1 selection, the required value can be cell, expression, text.
- [criteria_range2, criteria2] are selections and additional conditions, which allow up to 127 selection pairs and conditions.
Note when using the COUNTIFS function:
- Additional selections need to have the same number of rows and columns with the criteria_range1 area, which can be separated.
- If the selection condition refers to an empty cell, the COUNTIFS function automatically evaluates to 0.
- 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:
- C2: C7 is the area of counting 1 required for gender of employees.
- Male is the condition of the count area 1.
- C2: C7 is the counting area 2 with the Employee's Day.
- 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!
You should read it
- COUNTIFS function in Excel
- How to use COUNTIF function on Excel
- COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel
- Basic Excel functions that anyone must know
- DCOUNTA function, how to use the function to count non-empty cells in Excel
- Instructions on how to count words in cells in Excel
- How to use the LEN function in Excel
- How to use the COUNT function in Excel
- The COUNTA function, how to use the function to count cells containing data in Excel
- How to count characters in Excel
- How to use Hlookup function on Excel
- Instructions on how to use the Dmax function in Excel
May be interested
Need an alternative Google Suite solution? This is why you should use Zoho
DCOUNTA function, how to use the function to count non-empty cells in Excel
The SUMIFS function, how to use multiple conditional calculation functions in Excel
Differentiate between SUM, SUMIF, SUMIFS and DSUM functions
Instructions for writing vertical letters in Word
How to compare two documents in Google Docs