How to Count Matching Values in Non-Contiguous Ranges in Excel
In this article, we’ll explore a technique for counting matching values in multiple non-contiguous ranges in Excel. While the COUNTIF function is powerful, it traditionally doesn’t handle multiple ranges or arrays. Users often resort to combining all data into a single range, making calculations cumbersome. We’ll present a streamlined solution to this common problem.
The Problem: Imagine needing to count specific values in multiple arrays. The traditional approach involves using separate COUNTIF formulas for each range and summing up the results. However, we’ll demonstrate a more efficient method using a single formula.
Solution:
We employ the COUNTIF, SUM, and INDIRECT functions to achieve our goal. Here’s the generic formula we’ll use:
=SUM(COUNTIF(INDIRECT({"list1", "list2", "list3", ...}), criteria))
Where list1, list2, and list3 are named ranges representing the non-contiguous data sets, and criteria is the value or condition you’re looking to match.
Example:
Consider three lists of names: list1 (C3:C7), list2 (E5:E8), and list3 (F4:F6). To count the occurrences of “Gillen,” we apply the formula:
=SUM(COUNTIF(INDIRECT({"C3:C7", "E5:E8", "F4:F6"}), "Gillen"))
Here’s how the formula works:
- INDIRECT({“C3:C7”, “E5:E8”, “F4:F6”}) evaluates and feeds the specified ranges to COUNTIF.
- COUNTIF checks each range for occurrences of “Gillen.”
- SUM then adds up all individual counts, giving us the total matches across non-contiguous ranges.
The result, in this case, would be 4, indicating there are four occurrences of “Gillen” across the three lists.
Note:
Wildcards can aid in extracting values from substrings.- When using the COUNTIF function, logical operators like <, >, <>, = must be enclosed in double quotes (“”).
- This formula works for both text and numerical data.
- This formula works for both text and numerical data.
By employing this technique, you can efficiently count matching values across non-contiguous ranges in Excel, providing a streamlined solution for complex data scenarios.
