Summary
To count visible rows with criteria, you can use a formula based on the MAP function and the SUBTOTAL function. In the example shown, the formula in H5 is:
=SUM((C5:C16=F5)*MAP(C5:C16,LAMBDA(c,SUBTOTAL(103,c))))
The result is 4 since there are four visible rows where the Region is "West". MAP is available in Excel 2024+ and Excel 365. For Excel 2021 and earlier, see the OFFSET formula at the end of the page.
Generic formula
=SUM((range=criteria)*MAP(range,LAMBDA(c,SUBTOTAL(103,c))))
Explanation
In this example, the goal is to count visible rows where Region="West", using the region in cell F5. Row 13 meets this criteria, but has been hidden, so it should be excluded. This is an interesting challenge, because Excel does not have a function that will return the visibility of a cell as a TRUE or FALSE value.
The SUBTOTAL function can easily generate sums and counts for visible rows. However, SUBTOTAL is not able to apply criteria like the COUNTIFS function without help. Conversely, COUNTIFS can easily apply criteria but is not able to distinguish between rows that are visible and rows that are hidden. You can see this in cell G5, where =COUNTIF(C5:C16,F5) returns 5, because COUNTIF counts the hidden row too.
One solution is to use Boolean logic to apply criteria, then use the SUBTOTAL function together with the MAP function to check visibility, and then add up the results with the SUM function. At the core, this formula works by setting up two arrays inside SUM. The first array applies criteria, and the second array handles visibility:
=SUM(criteria*visibility)
Table of contents
- Criteria
- Visibility
- Adding up results
- Multiple criteria
- Sum visible rows with criteria
- Excel 2019 and earlier
Criteria
The criteria are applied with this part of the formula:
=(C5:C16=F5)
Because there are 12 values in C5:C16 this expression generates an array with 12 TRUE and FALSE results like this:
{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
The TRUE values in this array indicate cells in C5:C16 that contain "West". When this array is multiplied by the array returned by MAP (described in detail below) the math operation coerces the TRUE and FALSE values to 1s and 0s, which creates this array:
{0;1;0;1;0;1;0;0;1;0;0;1}
This array is used to apply the criteria Region="West" in one of the last steps below. If you need partial matching, you can construct an expression using ISNUMBER + SEARCH, as explained here, to create the criteria array.
Visibility
To check visibility, we use this part of the formula:
MAP(C5:C16,LAMBDA(c,SUBTOTAL(103,c)))
At a high level, we are using the SUBTOTAL function with function_num set to 103, which causes SUBTOTAL to count visible cells, ignoring cells that are hidden with a filter, or hidden manually. The problem is that we want an array of results, not a single result. If we give SUBTOTAL the whole range, it returns one count for all 12 cells:
=SUBTOTAL(103,C5:C16) // returns 11
This means we need to feed cells into SUBTOTAL one at a time, a good job for the MAP function. MAP runs a LAMBDA calculation on each item in an array, and in this case it calls SUBTOTAL, configured to count visible cells:
LAMBDA(c,SUBTOTAL(103,c))
Given a range, MAP passes each cell to c (for cell) as a cell reference, which SUBTOTAL requires. Inside the LAMBDA, SUBTOTAL checks one cell at a time, so the result is 1 for a visible cell and 0 for a hidden one. MAP returns all 12 results in a single array like this:
{1;1;1;1;1;1;1;1;0;1;1;1}
The 0 is row 13, the hidden row. Since the only possible values are 1 and 0, this array is exactly what we need to multiply by the array we created for the criteria. For more on how MAP works, see the MAP function page.
With 103, SUBTOTAL counts cells that are not empty, so it returns 0 for an empty cell even when the row is visible. Check visibility in a column with no empty cells, or in the criteria column, as in this example: an empty cell can't match "West" anyway.
Adding up results
Finally, we are ready to add up the results. Both arrays explained above are delivered to SUM like this:
=SUM({0;1;0;1;0;1;0;0;1;0;0;1}*{1;1;1;1;1;1;1;1;0;1;1;1})
After the two arrays are multiplied, we have:
=SUM({0;1;0;1;0;1;0;0;0;0;0;1}) // returns 4
In the final step, SUM sums the array and returns 4 as a final result.
Multiple criteria
You can extend the formula to handle multiple criteria by multiplying more conditions like this:
=SUM((criteria1)*(criteria2)*MAP(range,LAMBDA(c,SUBTOTAL(103,c))))
In the worksheet below, the goal is to count visible rows where Region="West" and Sales are over the amount in G5 ($50,000). The formula in I5 is:
=SUM((C5:C16=F5)*(D5:D16>G5)*MAP(C5:C16,LAMBDA(c,SUBTOTAL(103,c))))
The second condition, D5:D16>G5, returns TRUE for each sale over $50,000:
{TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE}
Multiplying the arrays works like AND logic: a row is counted only when it is in the West region, has sales over $50,000, and is visible. The result is 3 (Tanaka, Delgado, and McDougal). The COUNTIFS formula in H5 returns 4, because it also counts Garcia in hidden row 13.
Sum visible rows with criteria
To return a sum of visible values (instead of a count), give MAP the column to sum, and set function_num to 109. In the worksheet below, the formula in H5 is:
=SUM((C5:C16=F5)*MAP(D5:D16,LAMBDA(c,SUBTOTAL(109,c))))
With 109, SUBTOTAL returns a sum, ignoring hidden rows. Since MAP passes one cell at a time, SUBTOTAL returns the amount for a visible row and 0 for a hidden one:
{52500;60000;48000;45000;49500;51250;38500;45005;0;43300;52500;54750}
The criteria array keeps only the amounts in the West region, and SUM returns $211,000. In contrast, the SUMIFS formula in G5 returns $267,000, because it includes the $56,000 in hidden row 13.
Excel 2019 and earlier
The formulas above need MAP, which Legacy Excel and Excel 2021 don't have. In these versions, you can use the original formula on this page, based on SUMPRODUCT, SUBTOTAL, and OFFSET:
=SUMPRODUCT((data=F5)*(SUBTOTAL(103,OFFSET(INDEX(data,1),ROW(data)-MIN(ROW(data)),0))))
Where data is the named range C5:C16. The generic version of the formula looks like this:
=SUMPRODUCT((criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
The criteria work the same way as explained above. The SUMPRODUCT function takes the place of SUM because it handles arrays natively, while SUM needs Control + Shift + Enter in older versions (see Why SUMPRODUCT?). The difference is how the formula gets 12 results from SUBTOTAL. Without MAP, 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:
OFFSET(INDEX(data,1),ROW(data)-MIN(ROW(data)),0)
Working from the inside out, reference is provided to OFFSET with the INDEX function like this:
INDEX(data,1) // first cell in data
We are simply asking INDEX for the first cell in the named range data, and INDEX (under the hood) returns C5 as a result:
OFFSET(C5,ROW(data)-MIN(ROW(data)),0)
The rows argument inside of OFFSET is created like this:
=ROW(data)-MIN(ROW(data))
={5;6;7;8;9;10;11;12;13;14;15;16}-5
={0;1;2;3;4;5;6;7;8;9;10;11}
Essentially, we are using this construction to create a zero-based index of offsets to give to the OFFSET function:
OFFSET(C5,{0;1;2;3;4;5;6;7;8;9;10;11},0)
Using these 12 row offsets, the OFFSET function returns an array of 12 cell references like this:
{C5;C6;C7;C8;C9;C10;C11;C12;C13;C14;C15;C16}
This array of references is returned to the SUBTOTAL function as the ref1 argument:
(SUBTOTAL(103,{C5;C6;C7;C8;C9;C10;C11;C12;C13;C14;C15;C16}))
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:
{1;1;1;1;1;1;1;1;0;1;1;1}
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 the criteria. To recap, the complexity above is needed to get to 12 results instead of the single result SUBTOTAL would give us if we provided a simple range. SUMPRODUCT then multiplies the two arrays and returns 4, as above.
Note: this SUBTOTAL and OFFSET technique comes from a MrExcel discussion with Mike Girvin and Aladin Akyurek. Mike also demonstrates it in Excel Magic Trick 1010 (YouTube).
To apply more criteria, multiply more conditions, as with the MAP formula. To return a sum of visible values, multiply by the range that contains the values you want to sum:
=SUMPRODUCT((criteria1)*(criteria2)*(SUBTOTAL(103,OFFSET(ref,rows,0,1))))
=SUMPRODUCT(criteria*visibility*sumrange)
