How to COUNTIF with Non-Contiguous Range in Excel. The COUNTIF function counts cells based on matching results but doesn’t accept multiple ranges or arrays. Users usually combine all data values into one array or range and calculate the count of values matched using a simple COUNTIF function. This is a common issue faced by most Excel users. Below is the solution for this problem.
How to COUNTIF with Non-Contiguous Range in Excel
Problem:
We need to use a single formula to calculate the count of matched values in multiple non-contiguous ranges or values in different arrays.
How to Solve the Problem:
There are two different approaches to accomplish this task.
Using INDIRECT function:
We will utilize the INDIRECT function to merge all the required ranges to include. Here, the formula will use three functions:
- COUNTIF function
- SUM function
- INDIRECT function
These functions will get the job done. The INDIRECT function feeds the COUNTIF function all the lists where values need to be matched with the criteria value. COUNTIF matches the criteria, and the SUM function returns the total count of values.
General Formula:
= SUM ( COUNTIF ( INDIRECT ( { "list1" , "list2" , "list3" , ... } ) , criteria ) )
Where:
- list1, list2 & list3… are named ranges for the multiple lists to be matched.
- criteria: value or condition to match.
Example:
Let’s understand how to use the function using an example. Suppose we have 3 lists containing names, and we need to count how many times the name “Gillen” is repeated.
Use the formula:
= SUM ( COUNTIF ( INDIRECT ( { "C3:C7" , "E5:E8" , "F4:F6" } ) , I3 ) )
Explanation:
- INDIRECT({“C3:C7”, “E5:E8”, “F4:F6”}) evaluates text values and feeds the ranges to the COUNTIF function.
- COUNTIF function returns each count for different ranges matching all the lists with the value in cell I3.
- SUM function returns the sum after adding all the counts.
This formula in the cell returns a count. Here, the values satisfying the condition in cell I3. The matched values must return counts for each individual: 1 from list1, 2 from list2, and 1 again from list3, resulting in a total of 4.
As seen in the example, the COUNTIF formula returns the count of cells with non-contiguous ranges in Excel. This formula can be used for both text and numerical values.
Individual Count for Each Range:
If needed, you can perform individual counts for each range. For instance, continuing with the previous example, here’s how you could count the number of times “Gillen” appears in each separate list:
= COUNTIF ( list1, I3 ) + COUNTIF ( list2, I3 ) + COUNTIF ( list3 , I3 )
Explanation:
- Multiple COUNTIF functions return counts for different ranges matching all the lists with the value in cell I3.
- The ‘+’ operator returns the sum after adding all the counts.
Example with Numerical Values:
Let’s consider another scenario where we have 3 lists of numbers, and we want to count the numbers greater than 50:
Use the formula:
= SUM ( COUNTIF ( INDIRECT ( { "C3:C7" , "E5:E8" , "F4:F6" } ) , I3 ) )
Explanation:
- INDIRECT({“C3:C7”, “E5:E8”, “F4:F6”}) evaluates values and feeds the ranges to the COUNTIF function.
- COUNTIF function returns each count for different ranges matching all the lists with the condition in cell I3.
- SUM function returns the sum after adding all the counts.
The formula counts the numbers greater than 50 in the specified ranges.
Similarly, you could perform individual counts for each range if needed.
Final Note:
How to COUNTIF with Non-Contiguous Range in Excel. This method provides a way to handle COUNTIF with non-contiguous ranges in Excel, allowing users to efficiently count values that span multiple arrays or ranges, whether dealing with text or numerical values.
