The result is 9, since there are nine values greater than 50 in the three ranges shown. Note: In Excel 365, the VSTACK and HSTACK functions offer a new approach to this problem. See below for an example.
INDIRECT and COUNTIF
The INDIRECT function converts a given text string into a proper Excel reference: One approach is to provide the ranges as text in an array constant to INDIRECT like this: Then pass the result from INDIRECT into the COUNTIF function like this: INDIRECT will evaluate the text values and pass the references into COUNTIF as the range argument. For reasons mysterious, COUNTIF will accept the result from INDIRECT without complaint. Because COUNTIF receives more than one range, it will return more than one result in an array like this: The three numbers in this array are the counts of numbers greater than 50 in each of the three ranges. To “catch” these results and return a total, we use the SUM function: The SUM function then returns the sum of all values, 9. Although this is an array formula, it does not require CSE, since we are using an array constant. Note: INDIRECT is a volatile function and can impact workbook performance.
Multiple COUNTIFs
Another way to solve this problem is to use more than one COUNTIF: With a limited number of ranges, this approach may be easier to implement. It avoids possible performance impacts of INDIRECT, and allows a normal formula syntax for ranges, so ranges will update automatically with worksheet changes. The INDIRECT example above relies on text strings that need to be updated manually.
VSTACK function
In current versions of Excel, a better approach is to first combine the ranges, then perform the conditional count. To combine all three ranges vertically, you can use the VSTACK function: The result from VSTACK is a single array with 14 values. Unfortunately, we can’t pass this result into the COUNTIF function, because COUNTIF is in a group of functions that require actual ranges. However, we can use the SUM function and Boolean algebra to perform the conditional count. The complete formula looks like this: After VSTACK runs, and all values are checked with >50, we have an array of 14 TRUE and FALSE values like this: The double negative (–) is used to convert the TRUE and FALSE values to 1s and 0s, and the resulting array is returned directly to the SUM function: The SUM function sums the array and returns 9 as a final result. Video: Boolean operations in array formulas
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
