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.

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 1

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:

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 2

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")

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 3

Press Enter -> the total number of apple items with unit price greater than 25000 is:

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 4

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")

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 5

- Press Enter -> Total items of Thailand with unit price greater than 25000 are:

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 6

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")

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 7

- Press Enter -> The total number of Thailand products with the unit price range from 35,000 to 70000 is:

COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel Picture 8

Above are instructions and some specific examples when using the COUNTIFS function in Excel.

Good luck!

5 ★ | 1 Vote

May be interested

  • How to use Hlookup function on ExcelHow to use Hlookup function on Excel
    hlookup 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 ExcelHow to use the SUBTOTAL function in Excel
    the 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 ExcelCOUNTIFS function in Excel
    the 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 function, how to use the function to count non-empty cells in Excel
    dcounta functions in excel to count non-empty cells in database fields or lists according to specific conditions.
  • How to use the COUNT function in ExcelHow to use the COUNT function in Excel
    the 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 ExcelInstructions on how to use the Dmax function in Excel
    the 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 ExcelHow to use the DAVERAGE function in Excel
    daverage 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 2016How to use MAXIFS function in Excel 2016
    maxifs 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 ExcelHow to automatically create valuable cell borders in Excel
    there 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 ExcelHow to use AVERAGEIF function in Excel
    averageif function on excel is a conditional plus average function in the data table, with the given conditions set by the user.