How to Use COUNTIF for Non Contiguous Range in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for How to Use COUNTIF for Non Contiguous Range in Excel


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.

Use Multiple COUNTIF Functions for Non Contiguous Range in Excel

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"))

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

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

COUNTIF vs COUNTIFS in Excel

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.

Practice Sheet for Excel COUNTIF for Non Contiguous Range


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo