Where data is the named range C5:C16. The result is 4, since there are 4 visible rows where the Region is “West”.
Overview
At the core, this formula works by setting up two arrays inside SUMPRODUCT. The first array applies criteria, and the second array handles visibility: The formula in H7 takes this approach:
Criteria
The criteria is applied with this part of the formula: Because there are 12 values in data (C5:C16) this expression generates an array with 12 TRUE and FALSE results like this: The TRUE values in this array indicate cells in data that contain “West”. When this array is multiplied by the array returned by the SUBTOTAL function (described in detail below) the math operation coerces the TRUE and FALSE values to 1s and 0s, which creates this final array of results: This array is used to apply the criteria Region=“West” in one of the last steps below.
Visibility
To check visibility, we use an expression like this: At a high level, we are using the SUBTOTAL function with function_num set to 103, which causes SUBTOTAL to count cells that are visible, ignoring cells that are hidden with a filter, or hidden manually. The reason the expression is complex is that we need an array of results, not a single result. This means we need to feed cells into SUBTOTAL one at a time using the OFFSET function. To do this, we have configured OFFSET to create the references needed for ref1 inside SUBTOTAL like this: Working from the inside out, reference is provided to OFFSET with the INDEX function like this: We are simply asking INDEX for the first cell in the named range data, and INDEX (under the hood) returns C5 as a result: The rows argument inside of OFFSET is created like this: Essentially, we are using this construction to create a zero-based index of offsets to give to the OFFSET function: Using these 12 row offsets, the OFFSET function returns an array of 12 cell references like this: This array of references is returned to the SUBTOTAL function as the ref1 argument: With function_num set to 103, SUBTOTAL returns the count of visible cells in each reference. Because each reference is provided separately, we get back an array with 12 counts like this: Since we are feeding the references to SUBTOTAL one at a time, the only possible values are 1 and 0, which is exactly what we need when we multiply this array by the array we created for criteria below. To recap, the complexity above is needed get to 12 results instead of the single result SUBTOTAL would give us if we provided a simple range.
Adding up results
Finally, we are ready to add up results. For this, we use the SUMPRODUCT function. Both arrays explained above are delivered to SUMPRODUCT like this: After the two arrays are multiplied, we have: In the final step, SUMPRODUCT sums the array and returns 4 as a final result.
Multiple criteria
You can extend the formula to handle multiple criteria like this:
Summing results
To return a sum of visible values (instead of a count), you can adapt the formula to include range of cells to sum like this: The sum range is the range that contains values you want to sum. The criteria and visibility arrays work the same as explained above, excluding cells that are not visible. If you need partial matching, you can construct an expression using ISNUMBER + SEARCH, as explained here, to create the criteria array.
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.
