Sometimes we may need to work with multiple ranges on the same criteria in terms of searching, counting, or sorting. In this way, Microsoft Excel helps us by providing a function named COUNTIF. In this article, I will show 5 simple ways on how to apply the COUNTIF function in multiple ranges.on the same criteria.
For more clarification, I have used a dataset where I have two tables where there are some players’ names along with country name and goals/assists numbers.
Download Practice Workbook
5 Easy Ways to Apply COUNTIF Function in Multiple Ranges for Same Criteria
If we want to count the number of cells with a certain criteria from multiple ranges, we need to follow some specific ways. There are 5 simple and easy ways that i am going to discuss in the following section
1. Use Multiple COUNTIF Function in Multiple Ranges for Same Criteria
In order to count certain results based on same criteria in multiple ranges, we can apply the COUNTIF function on multiple times. For this, you just need to follow the following procedures.
- Select a cell with certain criteria and input the following formula.
Here, I have applied the COUNTIF function to count the number of contributions in terms of goals or assists more than 50 in ranges D5:D9 and D13:D17.
- Now, press ENTER to have the output.
2. Insert Multiple COUNTIF in Multiple Ranges for Specific Criteria
We can also use COUNTIF in multiple ranges with a specific criteria to count a defined things. You can follow the steps mentioned below for this purpose.
- Firstly, pick a cell with a defined criteria and input the following formula to count the number of players with zero contributions.
=COUNTIF(D5:D9,0) + COUNTIF(D13:D17,0)
- Finally, hit the ENTER button to have the defined result.
3. Combine COUNTIF, SUMPRODUCT, & INDIRECT Functions in Multiple Ranges for Same Criteria
- Choose a cell first.
- Then, apply the following formula to count from multiple ranges with same criteria.
Here, I have count the cells holding Germany name from cells C5:C9 and C13:C17.
- For having the output, press ENTER.
- How to Apply COUNTIF Not Equal to Text or Blank in Excel
- Excel COUNTIF for Multiple Criteria with Different Column
- How to Use COUNTIF Function Across Multiple Sheets in Excel
- COUNTIF with Multiple Criteria in Different Columns in Excel
4. Use COUNTIFS in Multiple Ranges for Same Criteria
The COUNTIFS Function is a function where I can input criteria for multiple ranges. The process is given below.
- Input the following formula with the COUNTIFS Function to count the values from multiple ranges.
=COUNTIFS(C5:C9 : C13:C17,"=Argentina")
- You will have the output by pressing the ENTER button.
5. Application of COUNTIF for Same Date in Excel
In order to count cells with certain date criteria, there is a way too to count them with the COUNTIF function. For this, you just need to follow the following procedures.
- Input the following formula in a selected cell to count the cells with same criteria in multiple ranges.
Here, I have defined the formula to count the cells less than 1/1/1995 from the ranges C5:C9 and E5:E9.
- Finally, press the ENTER button to have the output.
For more expertise, you can practice here.
At the end of this article, I like to add that I have tried to explain 5 simple ways on how to apply the COUNTIF function in multiple ranges. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.