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.
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.
How to Use COUNTIF for Non Contiguous Range in Excel: 2 Easy Ways
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: COUNTIF Between Two Cell Values in Excel
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: Excel COUNTIF to Count Cells Greater Than 1
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 Calculate Frequency Using COUNTIF Function in Excel
Practice Section
Here, I have provided a practice sheet for you to practice how to use COUNTIF for a non contiguous range in Excel.
Download Practice Workbook
You can download the practice workbook from here.
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. Lastly, if you have any questions, feel free to let me know in the comment section below.
Related Articles
- How to Use COUNTIF Function to Calculate Percentage in Excel
- How to Use COUNTIF Function with Array Criteria in Excel
- Excel COUNTIF Function with Conditional Formatting
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use COUNTIF Function in Excel Greater Than Percentage
- How to Compare Two Columns Using COUNTIF Function
- How to Use Excel COUNTIF Between Time Range
- How to Use COUNTIF to Count Date Less Than Today in Excel
