I'm just trying to make a small visibility map for myself for my job but I'm not the biggest expert at Excel. Whenever I used =countifs with filters in Excel, my cells, for some reason, vanish. Is there a way to keep my cells in place when I use the filter function? The cells are counting correctly it's just if I want to filter the numbers within their ranges, they vanish.

Here is my Excel before using filters:

This is what it looks like when I filter data out.

CodePudding user response：

You've got your COUNTIFs on the same rows as your data. When you filter the data, it hides entire rows - including your COUNTIFS. As @Prema said, you could put the summary info on a separate sheet. Or if it's very compact, you could put the summary above the data.

CodePudding user response：

You can combine SUMPRODUCT and SUBTOTAL to get a count if and exclude values hidden (by filter).

For instance: Text Values Range A2:A7 Number Values Range B2:B7 Criteria Range D13 up to D16

Formula in E13: =SUMPRODUCT(($A$2:$A$7=D13)*(SUBTOTAL(103,OFFSET($B$2:$B$7,SEQUENCE(ROWS($B$2:$B$7),,0),,1))))

The sumproduct creates an array of the condition being TRUE / FALSE (1 / 0 respectively) and the subtotal 103 counts the visible range only. OFFSET makes sure the subtotal doesn't return 1 end result but an array (cell by cell). The array of the condition * the subtotal array is the conditional count result

Unhidden result:

Hidden result:

This could also be used for SUMIF(S) by changing 103 to 109 in the SUBTOTAL function.

PS I'm unable to test it with the actual filter, since the app version of Excel does not support that, but I'm pretty sure it works as expected.