Utilizing the COUNTIFS function to tally multiple conditions in Excel.

What is COUNTIFS? 

COUNTIFS is a function used to count the number of times all criteria are met for cells within multiple ranges. It's an advanced function derived from COUNTIF.

Applications of COUNTIFS in Excel:

• Used to count data cells based on various conditions.
• Brings professional operations when using Excel.
• Easily combined with other functions to efficiently solve tasks.

COUNTIFS Function Syntax

Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• criteria_range1 (mandatory): The data range you want to count. Contains numbers, an array, or a reference containing numbers.
• criteria1 (mandatory): This is the condition for criteria_range1.
• criteria_range2 (optional): The second data range.
• criteria2 (optional): The condition for the second data range.

Note:

• The second data range onwards must have the same rows or columns as the first data range. They don't need to be adjacent to each other.
• You can set up to 127 data ranges in the function.
• Excel will default to 0 if the data range is empty.

How to Use the COUNTIFS Function

Below are specific examples of using COUNTIFS in Excel. Take a look to understand how to apply the COUNTIFS function effectively.

Example 1: A clothing manufacturing unit distributes various types of clothes with different quantities. Count the number of shirts distributed with more than 40 pieces.

Step 1: In the reference cell, input the formula =COUNTIFS(B3:B8,'Shirt',D3:D8,'>40')

Step 2: Press Enter, and the result will be displayed.

Example 2: Use the COUNTIFS function to count the number of items shipped from May 20, 2021, with costs exceeding 5 million.

Step 1: In the reference cell, input the formula

=COUNTIFS(B3:B8,'Shirt',D3:D8,'>40')

Step 2: Press Enter, and the result will be displayed.

Example 3: Use the COUNTIFS function to count the number of pants between 40 and 80 pieces shipped before May 31.

Step 1: In the reference cell, input the formula =COUNTIFS(B3:B8,'Pants',D3:D8,'>=40',D3:D8,'<31/5/2021')

Step 2: Press Enter, and the result will be displayed.

Through the article, Mytour has provided you with information on how to use the COUNTIFS function in Excel effectively. Hopefully, you can apply this function efficiently in your work. Keep following Mytour's news regularly to gain more useful knowledge.