Apply COUNTIF Function in Multiple Ranges for Same Criteria

Get FREE Advanced Excel Exercises with Solutions!

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.

COUNTIF Multiple Ranges Same Criteria


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.

Steps:

  • Select a cell with certain criteria and input the following formula.
=COUNTIF(D5:D9,">50")+COUNTIF(D13:D17,">50")

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.

COUNTIF Multiple Ranges Same Criteria

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.

Steps:

  • 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.

COUNTIF Multiple Ranges Same Criteria

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.

Steps:

  • Choose a cell first.
  • Then, apply the following formula to count from multiple ranges with same criteria.
=SUMPRODUCT(COUNTIF(INDIRECT({"C5:C9","C13:C17"}),"Germany"))

Here, I have count the cells holding Germany name from cells C5:C9 and C13:C17.

  • For having the output, press ENTER.

COUNTIF Multiple Ranges Same Criteria

Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria


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.

Steps:

  • Input the following formula with the COUNTIFS Function to count the values from multiple ranges.
=COUNTIFS(C5:C9 : C13:C17,"=Argentina")

COUNTIF Multiple Ranges Same Criteria

  • You will have the output by pressing the ENTER button.

COUNTIF Multiple Ranges Same Criteria

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.

Steps:

  • Input the following formula in a selected cell to count the cells with same criteria in multiple ranges.
=COUNTIF(C5:C9,"<1/1/1995")+COUNTIF(E5:E9,"<1/1/1995")

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.

COUNTIF Multiple Ranges Same Criteria

Read More: How to Use COUNTIF Between Two Dates and Matching Criteria in Excel


Download Practice Workbook

For more expertise, you can practice here.


Conclusion

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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

2 Comments
  1. 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.

  2. 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.

    Example:
    =sum(countifs(b4:h4:b10:h10, {“A”}))

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo