This post will guide you how to** count a non-contiguous range with criteria in COUNTIF function **in Excel 2013/2016 or Excel office 365. How do I build a formula for counting non-contiguous range in Excel using COUNTIF function or User Defined Function in Excel.

Table of Contents

## 1. What is Non-Contiguous Range?

A non-contiguous range consists of tow of more separate ranges in your current worksheet. And this post will show you one formula that counting the non-contiguous range with criteria in Excel.

## 2. Generic Formula

If you want to count a non-contiguous range with criteria, and you can use the COUNTIF function in combination with the INDIRECT and the SUM function. The general formula is like this:

`=SUM(COUNTIF(INDIRECT({“range1″,”range2″,”range3”}),criteria))`

The syntax of Explanations is as below:

**SUM**– the sum function can be used to add up values in the given cells.**COUNTIF**– COUNT the number of cells that match a certain condition.**INDIRECT**– return a valid cell reference from a given text string.

## 3. COUNT Non-Contiguous Range Using COUNTIF

Assuming you have tree ranges **A1:A5**, **C1:C5**, and **E1:E5** in your worksheet and you wish to count the number of values that is greater than **80**. And you can use the below formula:

`=SUM(COUNTIF(INDIRECT({"A1:A5","C1:C5","E1:E5"}),">80"))`

**Let’s See That How This Formula Works:**

The **COUNTIF function** can only count the number of cells in a range that match a given condition in Excel. And if you want to use the COUNTIF function with non-contiguous ranges, and you will get an error value. The best method is that you can use the** INDIRECT function **to get a valid cell reference from an array list that contain multiple text string.

The multiple ranges that returned by the **INDIRECT function** are passed into **COUNTIF function**. As the COUNTIF function receives an array list, it will return an array result also.

Finally, you can use the **SUM function** to add up all items in an array.

You can also use another solution to count Non-contiguous range using **COUNTIF function**. And using more that one **COUNTIF function** to count the numbers. The formula is like this:

`=COUNTIF(A1:A5,">80")+COUNTIF(C1:C5,">80")+COUNTIF(E1:E5,">80")`

You can also use the **SUMPRODUCT function** along with the** COUNTIF function** to count non-contiguous ranges in Excel. Just use the following formula:

`=SUMPRODUCT(COUNTIF(INDIRECT({"A1:A5","C1:C5","E1:E5"}),">80"))`

This formula will count the number of cells that meet your criteria in the non-contiguous ranges that you specify.

## 4. Count Non-Contiguous Range with VBA Code

You can count non-contiguous ranges in Excel with VBA code (user-defined function), just do the following steps:

**Step1:** Press the **Alt** and **F11** keys at the same time to open the VBA editor.

**Step2:** In the VBA editor, select “**Insert**” from the menu bar and choose “**Module**” to create a new module.

**Step3:** Copy and paste the VBA code for the **user-defined function** into the module. Save the workbook with the VBA code.

Function CountValuesAboveCriteria_excelHow(rngArray As Variant, criteria As Variant) As Long Dim countArray() As Long ReDim countArray(LBound(rngArray) To UBound(rngArray)) Dim i As Long For i = LBound(rngArray) To UBound(rngArray) countArray(i) = Application.CountIf(Range(rngArray(i)), criteria) Next i CountValuesAboveCriteria_excelHow = Application.Sum(countArray) End Function

**Step4:** Close the VBA editor and return to the worksheet where you want to use the function.

**Step5:** In any blank cell, enter the formula:

`= CountValuesAboveCriteria_excelHow(rngArray, criteria) `

Where “**rngArray**” is an VBA array of range strings (**e.g. {“A1:A5″,”C1:C5″,”E1:E5”}**) and “**criteria**” is the criteria string (e.g. “**>80**“).

`=CountValuesAboveCriteria_excelHow({"A1:A5","C1:C5","E1:E5"},">80")`

**Step6:** Press **Enter** to see the result.

**Note**that you need to replace “

**rngArray**” and “

**criteria**” with the appropriate values when using the function.

## 5. Video: Count Non-Contiguous Range in excel

This video will demonstrate both formula and VBA code methods for counting non-contiguous ranges in Excel.

## 6. Related Functions

- Excel SUM function

The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)… - Excel COUNTIF function

The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)… - Excel INDIRECT function

The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….