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.
Read More: How to Apply COUNTIF Function in Excel with Multiple Criteria
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.
Read More: Excel COUNTIF Function with Multiple Criteria & Date Range
3. Combine COUNTIF, SUMPRODUCT, & INDIRECT Functions in Multiple Ranges for Same Criteria
There is another very effective way that combines the COUNTIF, SUMPRODUCT, and INDIRECT functions in order to count cells for same criteria in multiple ranges.
- 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.
Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria
- How to Use COUNTIF for Cells Not Equal to Text or Blank in Excel
- Excel COUNTIF for Multiple Criteria with Different Column
- How to Use Excel COUNTIF Between Time Range (2 Examples)
- 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.
Read More: COUNTIF That Does Not Contain Multiple Criteria in Excel
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.
Read More: How to Use COUNTIF Between Two Dates and Matching Criteria in Excel
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.
- Apply SUM and COUNTIF for Multiple Criteria in Excel
- How to Find Intersection of Two Lists in Excel (3 Easy Methods)
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
- How to Use COUNTIF Function with Array Criteria in Excel
- COUNTIF Between Two Values with Multiple Criteria in Excel
- Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)
- How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
The part under “4. Using COUNTIFS Function” is incorrect and misleading.
Specifying the input range as C4:C10:E4:E10 is exactly the same specification as C4:E10. Meaning in this way of writing you are referring to a single range and not to “Multiple Ranges Same Criteria in Excel” as the title is promising. You better remove this section.
Related to comment above and my experience. Is it possible to select two different ranges not near each other?
As when I attempt this it shows the two different ranges highlighted however the formulas returns other ranges in between.