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.

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

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

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

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

**Step 2:** 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.

**[ 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**.

**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

**this portion we are counting the cells from the given multiple ranges which are greater than**

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

**$500**. Lastly, the

**SUMPRODUCT**returns the total number of cells which are satisfied the condition.

Let’s try if the above formula works for different 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**.

**Step 2: **See the result.

**Points to be Noted**

- By default,
**COUNTIFS**applies the ’and’ logic among the different criteria given. **COUNTIFS**and**COUNTIF**will also work if the cells are not adjacent.**COUNTIFS**gives the count of the number of rows whose cells meet the given criteria.- 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.