Explanation
In this example, the goal is to count values in three non-contiguous ranges with criteria.
To be included in the count, values must be greater than 50.
The COUNTIF counts the number of cells in a range that meet the given criteria.
However, COUNTIF does not perform counts across different ranges.
There are several ways to approach this problem, as explained below.
For reasons mysterious, COUNTIF will accept the result from INDIRECT without complaint.
Although this is an array formula, it does not requireCSE, since we are using an array constant.
Note: INDIRECT is avolatile functionand can impact workbook performance.
The INDIRECT example above relies on text strings that need to be updated manually.
However, we can use the SUM function andBoolean algebrato perform the conditional count.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
VSTACK Function
The Excel VSTACK function combines arrays vertically into a single array.
Each subsequent array is appended to the bottom of the previous array….
