Cách dùng hàm Countifs trong Excel

COUNTIFS is one of the familiar functions commonly used in Excel. However, most people often have difficulty when they first get used to this function. Understanding that, Hoang Ha Mobile will guide you on how to use the COUNTIFS function to count with conditions, and at the same time introduce some other counting functions in Excel in the most detailed way through the article below. Please refer to it!

What is the COUNTIFS function in Excel?

Before we get to how to use the COUNTIFS function, let’s learn what COUNTIFS is. COUNTIFS is a function that counts the number of occurrences of objects that meet the conditions set forth in certain ranges of a spreadsheet.

Bạn đang xem: Cách dùng hàm Countifs trong Excel

COUNTIFS is a function that counts the number of occurrences of an object that meets given conditions.

It can be said that COUNTIFS is an advanced function of COUNTIFS. If COUNTIF only counts cells in a range with a single condition, COUNTIFS has the ability to count cells that meet multiple conditions at the same time in the same range or different given ranges.

In Excel, COUNTIFS is used to:

• Count data cells based on various conditions.
• Create professional and fast calculations.
• Allows combining with other functions in Excel to improve work efficiency.

Syntax and usage of the COUNTIFS function

The syntax and usage of the COUNTIFS function is:

COUNTIFS function syntax

The syntax of the COUNTIFS function is:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

In there:

• criteria_range1: This is a required element, used to define the range you want to count, which can contain numbers, an array of data, or a reference containing numbers.
• criteria1: Is a required component in the function, understood as the condition that the cell must meet in criteria_range1.
• criteria_range2: This is an optional component in the function, used to specify the second data range to count.
• criteria2: Similarly, this member is only available if you use criteria_range2 because it is the criteria of the second data range to count cells.

When using COUNTIFS in Excel, you don’t need to memorize the above syntax. Because Excel will automatically display each argument as you type the function name.

Syntax of the COUNTIFS function

Note:

• You can use COUNTIFS to count cells that meet a single condition in a given range or multiple conditions across multiple data ranges. In the latter case, the function will count only cells that meet all of the specified conditions.
• Each additional data range must have the same number of rows and columns as the initial data range – the criteria_range1 argument, respectively. However, they do not have to be contiguous.
• The COUNTIFS function counts up to 127 data ranges.
• COUNTIFS will automatically treat a range specified as empty cells as a value of 0.
• You can use wildcard characters such as asterisks.

and question mark (?).

How to use the COUNTIFS function

To understand how to use the COUNTIFS function, you can follow the example below:

Case 1

For example, your company is manufacturing and distributing many different types of items (shirts, pants) and you need to count the number of shirts exported with the condition that the quantity must be over 40. Then, you enter the following syntax in the reference box and press Enter to see the result:

This function means:

Reference range 1: From cell B3 to cell B8.

Condition to be met to count in reference zone 1: The word “Shirt” is present.

Reference range 2: From cell D3 to cell D8.

Xem thêm : Top 5 máy lạnh Panasonic 1 ngựa (1HP) đáng mua nhất

You need to count the number of types of goods that have been exported after May 20, 2021 with a price of over 5 million VND. The counting syntax will be:

This function means:

Reference range 1: From cell E3 to cell E8.

Condition to be met to count in reference zone 1: Has a value greater than or equal to 5000000.

Reference area 2: From cell F3 to cell F8.

how-to-use-countifs-6-function

The result of the calculation

=> Thus, the counted cell must satisfy both of the above conditions. After you press Enter, the result will be 2 (including: Pants in the North and Shirt in the South).

Case 3

Count the number of pants with quantity between 40 – 80 that were shipped before May 31, 2021. Using the COUNTIFS function, type the following syntax into the reference cell and press Enter to get the result:

how-to-use-countifs-7-function

Syntax to count pants with quantity between 40 – 80 pieces shipped before May 31, 2021

• This function means:
• Reference range 1: From cell B3 to cell B8.
• Condition to be met to count in reference zone 1: Contains the word “Pants”.
• Reference range 2: From cell D3 to cell D8.
• Conditions to be met to count in reference zone 2: Between 40 – 80.
• Reference area 3: From cell F3 to cell F8.

how-to-use-countifs-8-function

The results of the cells that satisfy the calculation

Thus, the counted cell must satisfy all three conditions above. After pressing Enter, the final result will be 1 because there is only one item that meets the requirements, which is: Shirts in the North.

Some other counting functions in Excel

In addition to using the COUNTIFS function, you can refer to some other counting functions in Excel as follows:

Types of counting functions in Excel

• In Excel there are 5 types of counting functions used in different cases, specifically:
• COUNT function: The function counts cells in a spreadsheet that contain numbers.
• COUNTIF: The function counts cells in a spreadsheet that meet a condition.
• COUNTIFS: We have understood how to use the COUNTIFS function through the above instructions. This is a function that counts cells that meet multiple conditions at the same time.
• COUNTA: The function counts non-empty cells in an Excel spreadsheet.

how-to-use-countifs-9-function

There are many different counting functions in Excel.

Conditional Counting Function

COUNTIF and COUNTIFS are two conditional counting functions. In which, the COUNTIFS function is an advanced function of COUNTIF. Because the COUNTIF function only counts cells that meet exactly 1 condition, the COUNTIFS function allows you to combine many different conditions.

The above section has instructed how to use the COUNTIFS function, so in this section we will learn about the COUNTIF function:

Syntax: =COUNTIF(range, criteria)

• In there:
• Range: The reference range to count.

Criteria: The condition that must be met to count.

For example, you need to count how many items are in stock out of 150 products in the spreadsheet below. Then the syntax would be:

Note:

You can use wildcard characters like question mark (?) will represent any character and asterisk

represents any number of ideas.

To find conditions that are asterisks or question marks, you precede them with a ~ (such as “~?”, “~*”).

The COUNTIF function in Excel is not case sensitive.

COUNTA function to count non-empty cells

Unlike the COUNTIFS or COUNTIF functions, the COUNTA function is used to count cells in a spreadsheet that contain strings or numbers – that is, non-blank cells within a specified range.

The formula would be:

=COUNTA(value1, value2,…)

For example: You need to know the number of students who have completed the assignment in the statistics table below. It is known that students who have completed and submitted the assignment completely are those who have attached the assignment link in the column “Link of submitted assignment”.

how-to-use-countifs-function-11

COUNTA function to count non-empty cells

Now, you enter: =COUNTA(C2:C15) in cell F2 and the result will show that 6/14 of you have submitted your assignment.

Count blank cells with the COUNTBLANK function

Unlike the COUNTIFS, COUNTIF functions and even the opposite of the COUNTA function, COUNTBLANK is a function used to count cells that do not contain data – that is, blank cells in a pre-defined data range.

The syntax of the COUNTBLANK function is: =COUNTBLANK(range)

For example: With the same table above, but you need to know the number of students who have not done their homework – that is, those who have not filled in the path in row C, we will have the syntax: =COUNTBLANK(C2:C15).

how-to-use-countifs-12-function

COUNTBLANK function to count empty cells

So, from the count below, 8/14 of you have not submitted your homework.

• Some notes to know when using the count function in Excel
• Here are some notes you need to pay attention to when using the count function:
• The COUNTIF function is not case sensitive for text. For example, cells containing “study”, “STUDY” or “Study” will all match and be counted.
• When applying the COUNTIFS and COUNTIF functions, you can use the special character asterisk.
• and question mark (?). In which, question mark (?) represents a single character, and asterisk
• represents any string of characters.

If you count text, you need to make sure that the data you enter does not have spaces before or after.

You can name a specified data area/range with the COUNTIF function. The naming method is: Select the data area you want to name > Right-click > Select Define Name > Enter the data area name in the Name box > Click OK to complete. In addition, you are also allowed to replace the data area range with the name of the range set inside the syntax.

Named ranges can be located within the same worksheet or between two different worksheets, in the same workbook or in another workbook, and you can reference them back and forth, as long as both workbooks are open.

how-to-use-countifs-function-13

You can use named ranges with the COUNTIF functionEpilogueThrough the above article, in addition to using COUNTIFS, Hoang Ha Mobile has shared some other counting functions in Excel for you to refer to and apply in each specific case. Hopefully the article will be useful and don’t forget to share it for everyone to read!see more

Nguồn: https://tuyengiaothudo.vn
Danh mục: Thủ thuật