COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel
The following article introduces you to the COUNTIFS function - one of the functions in the statistical function group is very popular in Excel.
Description: The function to count the number of cells in a data table satisfies many conditions.
Syntax: COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], .)
Inside:
- criteria_range1: The first range of condition 1, is the required parameter.
- criteria1: The condition that identifies a cell or reference to be counted, which is a required parameter.
- criteria_range2, criteria2: The second data range you want to count corresponding to the second condition ( criteria2). Are optional values containing up to 127 pairs of ranges and conditions.
Attention:
- If the criteria condition refers to an empty cell -> the function considers that cell to be 0.
- Use the characters?, * To combine in counting conditions.
For example:
Calculate the total number of items described in the table below:
1. Calculating the total number of apple products with unit prices greater than 25000.
- In the cell to calculate, enter the formula : = COUNTIFS (C6: C10, "Apple *", D6: D10, "> 25000")
Press Enter -> the total number of apple items with unit price greater than 25000 is:
2. Calculate the total number of Thai products with unit prices greater than 25,000.
- In the cell to calculate enter the formula: = COUNTIFS (C6: C10, "* Thailand", D6: D10, "> 25000")
- Press Enter -> Total items of Thailand with unit price greater than 25000 are:
3. Calculate the total number of Thai products that have a unit price between 35,000 and 70000.
- In the cell to calculate enter the formula: = COUNTIFS (C6: C10, "* Thailand", D6: D10, "> 35000", D6: D10, "<70000")
- Press Enter -> The total number of Thailand products with the unit price range from 35,000 to 70000 is:
Above are instructions and some specific examples when using the COUNTIFS function in Excel.
Good luck!
You should read it
- The COUNTIFS function, how to use the cell count function according to multiple events in Excel
- Cell counting function with data in Excel - Enclose example
- COUNTA function - The function performs counting non-blank cells in the list of arguments in Excel
- COUNTBLANK function - The function performs counting empty cells in the list of arguments in Excel
- How to use Hlookup function on Excel
- Conditional counting function in Excel
- How to use COUNTIF function on Excel
- SUMIFS function in Excel
May be interested
- How to use Hlookup function on Excelhlookup function basically also has the function syntax and features like vlookup function, which is to help users find data in excel table, with the conditions or given information. here is the guide for using the hlookup function in detail.
- How to use the SUBTOTAL function in Excelthe subtotal function in excel is used in many different cases, helping you to sum subtotals in a list or database, unlike the sum function in excel such as counting cells, calculating average, finding the largest / smallest value. or sum the filtered list values in excel
- COUNTIFS function in Excelthe count () function counts cells that contain numbers and the countif () function counts cells according to a specified condition. but sometimes you need to count cells under many conditions, it is not possible to apply these two functions. in this case, you need to use the countifs () function to perform it.
- 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.
- How to use the COUNT function in Excelthe count function in excel is the basic counting function that is often used to count how many cells are in an area or in the entire spreadsheet.
- Instructions on how to use the Dmax function in Excelthe dmax function in excel is used to find the maximum value in the data table with conditional depending on the conditions that the user uses.
- How to use the DAVERAGE function in Exceldaverage in excel is a function that calculates the average value of data with given conditions in a spreadsheet. so, how to use this function?
- How to use MAXIFS function in Excel 2016maxifs function in excel is a statistical function that returns the largest value based on one or more conditions from the specified cells. maxifs function was introduced in ms excel 2016.
- How to automatically create valuable cell borders in Excelthere are many ways of highlighting valuable cells in excel, such as creating borders for cells automatically when entering values.
- How to use AVERAGEIF function in Excelaverageif function on excel is a conditional plus average function in the data table, with the given conditions set by the user.