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.
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.
As a result, we have 2 employees, Nam who has 25 man days.
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.
Results show 1 male employee has 0 holidays.
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 result is that 2 male employees who meet the requirements have a holiday of less than or equal to 2 days.
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 result is 2 Taiwanese products in accordance with the requirements.
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.
As a result, two Taiwanese products will be priced between VND 100,000 and VND 150,000.
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 - 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
May be interested
- Need an alternative Google Suite solution? This is why you should use Zohowant to replace google's suite of products but don't like to mix other random apps together? do you need a package that has all the applications? that's exactly what the zoho cloud suite provides.
- DCOUNTA function, how to use the function to count non-empty cells in Exceldcounta functions in excel to count non-empty cells in database fields or lists according to specific conditions.
- The SUMIFS function, how to use multiple conditional calculation functions in Excelthe sumifs function in excel calculates multiple conditions, combining multiple conditions.
- Differentiate between SUM, SUMIF, SUMIFS and DSUM functionswhenever you enter a function = sum in a cell in excel, you will get a lot of functions starting with sum and wondering how they are different? this article will help you solve that question.
- Instructions for writing vertical letters in Wordwrite vertical letters in word to create artistic effects for documents. so how to write vertical text in word?
- How to compare two documents in Google Docsgoogle has added an interesting feature, making it easier to work with multiple documents. the new feature will allow users to compare two documents and find the difference between the two documents.