**The COUNTIF function** is one of the statistical functions. This function is used to count cells that meet a certain criterion. The focus of this article is to explain how to use **COUNTIF for non contiguous range **in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## What Is Non Contiguous Range in Excel?

The **non contiguous range** in Excel consists of two or more sections of cells. These sections of cells can be separated by **columns** or **rows**. You can **select a non contiguous range** by selecting a section and then pressing and holding the **Ctrl** key while selecting other sections.

## 2 Easy Ways to Use COUNTIF for Non Contiguous Range in Excel

Here, I have taken the following dataset to explain this article. This dataset contains the **Sales** overview for **2** different** States**. I will use this dataset to show you how you can use **COUNTIF for non contiguous range** in Excel in **2** easy ways.

### 1. Use Multiple COUNTIF Functions for Non Contiguous Range in Excel

In this first method, I will use multiple **COUNTIF** functions to get results for a **non contiguous range **in Excel. In the following picture, you can see that the dataset contains the **Sales** overview for **Texas **and** Florida**. Here, I will count the** No. of Sales** for both of the** States** if the** Sales** are greater than **$1000**.

Let’s see how you can do that.

**Steps:**

- Firstly, select the cell where you want the result. Here, I selected
**Cell C15**. - Secondly, in
**Cell C15**write the following formula.

`=COUNTIF(C6:C12,">1000")+COUNTIF(F6:F10,">1000")`

- Thirdly, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**COUNTIF(C6:C12,”>1000″):**Here, in the**COUNTIF**function, I selected**C6:C12**as the**range**and**“>1000”**as the**criteria**. The formula returns the number of cells from cell range**C6:C12**that are**greater than 1000**.**COUNTIF(F6:F10,”>1000″):**Now, in the**COUNTIF**function, I selected**F6:F10**as the**range**and**“>1000”**as the**criteria**. The formula returns the number of cells from the cell range**F6:F10**that are**greater than 1000**.**COUNTIF(C6:C12,”>1000″)+COUNTIF(F6:F10,”>1000″):**Finally this formula returns the**summation**of the numbers it got from the**COUNTIF**functions.

**Read More:** **Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)**

**Similar Readings**

**How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel****Excel COUNTIF Function with Conditional Formatting (7 Examples)****How to Apply Excel COUNTIF with Pivot Table Calculated Field****Excel COUNTIF to Count Cell That Contains Text from Another Cell****How to Use COUNTIF Function in Excel Greater Than Percentage**

### 2. Employ Excel SUM and INDIRECT Functions to Use COUNTIF for Non Contiguous Range

Here, I will use **the SUM function** and **the INDIRECT function** to Use **COUNTIF** for a **non contiguous range** in Excel. Let’s see the steps of the calculation.

**Steps:**

- In the beginning, select the cell where you want the result. Here, I selected
**Cell C15**. - Then, in
**Cell C15**write the following formula.

`=SUM(COUNTIF(INDIRECT({"C6:C12","F6:F10"}),">1000"))`

- In the end, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**INDIRECT({“C6:C12″,”F6:F10”}):**Here, the**INDIRECT**function returns the valid cell references for these ranges.**COUNTIF(INDIRECT({“C6:C12″,”F6:F10″}),”>1000″):**Now, the**COUNTIF**function returns results for both of these ranges.**SUM(COUNTIF(INDIRECT({“C6:C12″,”F6:F10″}),”>1000″)):**Here, the**SUM**function**sums**the results it got from the**COUNTIF**function.

**Read More:** **How to Use Excel COUNTIF Between Time Range (2 Examples)**

## COUNTIF vs COUNTIFS in Excel

In this section, I will explain the difference between the **COUNTIF **function and** the COUNTIFS function**. Here, I am using** multiple ranges**. You know that **COUNTIFS** are suitable for **multiple ranges** but I am not using the **COUNIFS** function. Because there is a difference between **COUNTIF** **and COUNTIFS** **functions**. **COUNTIFS** follow the **AND** logic and by using multiple **COUNIF** we can get results for **OR** logic. Here, I have taken the following dataset. It contains the **State**, **Department**, and **State**. There are **2** **Criteria **mentioned here. I will calculate **No. of Sales** for these **2 Criteria **using both **COUNTIF** and **COUNTIFS** functions and you will be able to see the difference yourself.

Let’s see the steps of the calculation.

**Steps:**

- First, select the cell where you want the
**No. Of Sales**. - Then, write the following formula in the selected cell.

`=COUNTIF(B5:B11,C14)+COUNTIF(C5:C11,C15)`

- In the following step, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**COUNTIF(B5:B11,C14):**Here, in the**COUNTIF**function, I selected**B5:B11**as the**range**and**C14**as the**criteria**. The formula returns the number of cells from cell range**B5:B11**that that match the value in**Cell C14**.**COUNTIF(C5:C11,C15):**Now, in the**COUNTIF**function, I selected**C5:C11**as the**range**and**C15**as the**criteria**. The formula returns the number of cells from cell range**C5:C11**that that match the value in**Cell C15**.**COUNTIF(B5:B11,C14)+COUNTIF(C5:C11,C15):**Finally this formula returns the**summation**of the numbers it got from the**COUNTIF**functions.

- After that, select the cell where you want the
**No. of Sales**using the**COUNTIFS**function. Here, I selected**Cell C18**. - Then, in
**Cell C18**write the following formula.

`=COUNTIFS(B5:B11,C14,C5:C11,C15)`

- Finally, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**COUNTIFS(B5:B11,C14,C5:C11,C15):**Here, in the**COUNTIFS**function, I selected cell range**B5:B11**as**criteria_range1**,**C14**as**criteria1**,**C5:C15**as**criteria_range2**, and**C15**as**criteria2**. Now, the formula counts the number of cells in these**2**ranges that match both of the criteria.

**Read More:** **How to Use Excel COUNTIF That Does Not Contain Multiple Criteria**

## Practice Section

Here, I have provided a practice sheet for you to practice how to use **COUNTIF for a non** **contiguous range **in Excel.

## Conclusion

So, you have reached the end of my article. Here, I tried to cover how to use **COUNTIF for a non contiguous range **in Excel in **2** different ways. Moreover, I have also added the practice workbook at the beginning of the article. I hope this article was helpful to you. For more articles stay connected with **ExcelDemy**. Lastly, if you have any questions, feel free to let me know in the comment section below.

## Related Articles

**VBA COUNTIF Function in Excel (6 Examples)****How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)****COUNTIF Excel Example (22 Examples)****Apply COUNTIF Function in Multiple Ranges for Same Criteria****Excel COUNTIF with Greater Than and Less Than Criteria****Excel COUNTIF Function to Count Cells Greater Than 0**