COUNTIF Multiple Ranges Same Criteria in Excel

COUNTIF Multiple Ranges Same Criteria in Excel

Sometimes we may need to work with multiple ranges on the same criteria in terms of searching, counting, or sorting. In this way, MS Excel helps us by providing a function named COUNTIF. In this article, I will show how to use the COUNTIF function to count from multiple ranges on the same criteria. 

Download the Practice Workbook

What is COUNTIF Function

The syntax of the function is as follows:

COUNTIF (range, criteria)

In this function both the arguments in the parameter are mandatory. Firstly, it takes the range of cells that will be counted. The second section takes the criteria which is the condition. Based on this condition the counting will be executed. For more details visit this page Link

5 Ways to Handle Multiple Ranges with Same Criteria Using COUNTIF in Excel

1. Using Multiple COUNTIF or COUNTIF OR Method

For showing this example we need to consider multiple tables of the dataset. Let’s assume we have two tables of Salesperson’s data. From these different tables, I will show how to count the number of sales that are more than $500. I will use multiple COUNTIF functions for each condition to find our result.

Step 1: Enter the formula in cell G4.

=COUNTIF(B4:B12,">500")+COUNTIF(E4:E12,">500")

Formula Explanation

In this formula, I have used two COUNTIF as we want to get the result from two different tables. COUNTIF(B4:B12,”>500″) this portion returns the value from Table 1 and COUNTIF(E4:E12,”>500″) this portion return the value from Table 2. After that, we are adding the total cell numbers which are the return values of the two inner functions.

Using multiple COUNTIF

2. How to Count Dates from Multiple Range Using COUNTIF

Let’s assume the same dataset above and with another extra attribute which is Dates. Now instead of sales, I will illustrate how to count cells which is the date after 9/9/2020 from the two tables.

How to Count Dates from Multiple Range Using COUNTIF

Step 1: Enter the formula in cell I4 and press Enter.

=COUNTIF(C4:C12,">9/9/2020")+COUNTIF(G4:G12,">9/9/2020")

Formula Breakdown

Similar to the previous formula, here I have also used multiple COUNTIF functions. COUNTIF(C4:C12,”>9/9/2020″) this part extract the number of dates from Table 1 and COUNTIF(G4:G12,”>9/9/2020″) for Table 2.

Enter the formual using COUNTIF to find out dates from two different tables

3. How to Use COUNTIF on a Non-Contiguous Range of Cells

This method is similar to method 1 but here I will show with a different example. Let’s think we have a dataset of some Products with their Numbers of Sold, Transfer To, and In Stock. Now our task is to find out the number of products where Sold Number and In Stock is 0.

How to Use COUNTIF on a Non-Contiguous Range of Cells

Step 1: Enter the formula in cell B14 and press Enter.

=COUNTIF(C4:C10,0) + COUNTIF(E4:E10,0)

Formula Explanation

As we need to find out 0 values from two-column in the table which are C and E that’s why our range is C4 to C10 and E4 to E10.

COUNTIF(C4:C10,0) this part is searching and counting 0 in the Numbers of the Sold column and COUNTIF(E4:E10,0)  for In Stock column doing the same thing. Then we are just adding the total value.

Enter the formual using COUNTIF function to How to find the cell in a Non-Contiguous Range of Cells

Step 2: All the cells where No of sold and in stock is 0 will be counted.

All the cells where No of sold and in stock is 0 will be counted

4. Using COUNTIFS Function 

Instead of using the COUNTIF function, we can do the same thing using the COUNTIFS function easily. This section will show the process to use this function for multiple ranges on the same condition or criteria. First, see the fundamentals of the COUNTIFS function.

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

This is almost the same as the previous function COUNTIFS but the only basic difference is that it can take multiple ranges and conditions at a time in its parameter as an argument. In summary, it can be an alternative to multiple COUNTIFS functions. For more details visit this Link

For explaining this process let’s consider the same dataset used in the method 3.

Step 1: Enter the formula in cell B14.

=COUNTIFS(C4:C10:E4:E10,"=0")

Formula Explanation

In this formula, our multiple ranges are C4 to C10 and E4 to E10. I have passed these ranges in the argument of the function as C4:C10:E4:E10 and our condition is “=0” which is the same for both ranges. 

Enter formula using COUNTIFS

[ Tip: When you need to use multiple ranges on the same criteria or multiple ranges on multiple criteria then COUNTIFS is the best choice and time, saver. But make sure that you are using Excel 2019, 2016, 2013, Excel 2010, and Excel 2007, any of them to get the benefits of this function.]

5. How to Calculate Multiple Disjoint Ranges in Same Criteria Using COUNTIF

Let’s consider the same example which was used in method 1. But here I will do the same thing using some extra functions additionally with COUNTIF.

 How to Calculate Multiple Disjoint Ranges in Same Criteria Using COUNTIF

Step 1: Enter the formula in G4 and press Enter

=SUMPRODUCT(COUNTIF(INDIRECT({"B4:B12","E4:E12"}),">500"))

Formula Explanation

Here I have used 2 more functions additionally with the COUNTIF function. Before the main explanation first, we need to know the fundamentals of SUMPRODUCT and INDIRECT. 

SUMPRODUCT(array1, [array2], [array3], …)

It takes the array in its parameter and returns the sum. In this function, we can pass multiple arrays to get the total sum product. For more details, you can visit this link Link

INDIRECT(ref_text, [a1])

In the function’s first argument, we need to pass the reference text string to evaluate as a reference. [a1] indicates the reference style for the incoming text value which is optional. For more details, you can visit this Link

Now in the inner portion of the formula, INDIRECT({"B4:B12","E4:E12"}) we are managing the range of cells. As we cannot use multiple ranges in COUNTIF, I have used INDIRECT to combine multiple ranges. Then using COUNTIF(INDIRECT({"B4:B12","E4:E12"}),">500") this portion we are counting the cells from the given multiple ranges which are greater than $500. Lastly, the SUMPRODUCT returns the total number of cells which are satisfied the condition.

Enter the formula using COUNTIF in cell G4

Let’s try if the above formula works for different ranges.

Countif function for various ranges

I will use the same formula to find out the no of sales greater than $500.

Step 1: Enter the same formula in cell G18 and press Enter.

Enter the formula on cell G18.

Step 2: See the result.

Result for multiple range

Points to be Noted

  1. By default, COUNTIFS applies the ’and’ logic among the different criteria given.
  2. COUNTIFS and COUNTIF will also work if the cells are not adjacent.
  3. COUNTIFS gives the count of the number of rows whose cells meet the given criteria.
  4. Different special characters such as *, &, etc., can be used based on your requirements.

Conclusion

These are the ways to use the COUNTIF function with multiple ranges on the same criteria or condition. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of these functions and their most commonly used format codes. If you have any other method of achieving this then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

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

ExcelDemy
Logo