To count values in multiple ranges with criteria, you can use a formula based on SUM and VSTACK functions and the boolean logic.
How to count values in multiple ranges based on a criteria?
Here are the steps to count values in multiple ranges based on a criteria:
- Select cell H3
- Type =SUM(–(VSTACK(B3:B9,D3:D8,F3:F7)>30))
- Press Enter
- The formula counts all the values greater than 30 from the non-contiguous ranges B3:B9, D3:D8, and F3:F7
Example
In this example, we have multiple non-contiguous ranges of numerical data: Range1 (B3:B9), Range2 (D3:D8), and Range3 (F3:F7). The goal is to count how many values across all these ranges are greater than 30. The formula in cell H3 uses the VSTACK function to combine the values from the non-contiguous ranges into a single array. Then, a logical test checks which values are greater than 30, and the SUM function counts the occurrences of TRUE results.
Formula:
=SUM(–(VSTACK(B3:B9,D3:D8,F3:F7)>30))
The formula returns 4, indicating there are 4 numbers greater than 30 across all three ranges.
Explanation
Evaluate the formula:
VSTACK(B3:B9, D3:D8, F3:F7)
VSTACK combines multiple non-contiguous ranges (B3:B9, D3:D8, and F3:F7) into a single vertical array. VSTACK literally “stacks” these ranges on top of each other. The result is an array: {10; 20; 30; 44; 10; 30; 10; 20; 30; 50; 10; 30; 20; 30; 40; 55; 10}
VSTACK(B3:B9, D3:D8, F3:F7) > 30
This part of the formula compares every number in the stacked array against the condition >30. If the value is greater than 30, the comparison returns TRUE. Otherwise, it returns FALSE.
–(VSTACK(B3:B9,D3:D8,F3:F7)>30)
The double negative method converts the TRUE and FALSE results into 1 and 0, respectively.
Finally, the SUM function adds up all the 1s in the array to calculate the total count of values greater than 30. Total: 1 + 1 + 1 + 1 = 4
Workaround with COUNTIFS function
You can count values in multiple ranges using the built-in COUNTIFS function.
Formula:
=COUNTIFS(B3:B9,”>30″) + COUNTIFS(D3:D8,”>30″) + COUNTIFS(F3:F7,”>30″)
The COUNTIFS function counts the number of cells in a specified range that meet one or more criteria. In this formula, COUNTIFS is used three times to check different ranges (B3:B9, D3:D8, F3:F7) against the same criterion (“>30”). The + operator combines the results from the three COUNTIFS functions.
Each COUNTIFS returns a number, and the total sum represents the count of all cells across the specified ranges that satisfy the condition >30. If any values in the specified ranges change, the formula dynamically recalculates the count.