I have a long filtered list of numbers next to names. I am trying to count the zeros as I change the filter. Unfortunately COUNTIF includes cells that have been excluded by my filter. How can I count the zeros in my Numbers column only when they are not excluded by the filter?
Asked
Active
Viewed 1,720 times
1
3 Answers
1
Try including another column that checks if the cell value is a zero and returns 1 if true. You can use the function SUBTOTAL(9,RANGE) where RANGE is your added column that does the check for zero values. The 9 argument indicates the SUM function and returns the sum of cells based on whatever filter is applied.
Tom
- 196
0
This will only work within a very narrow understanding of your question, but if you filter the column with the zeros to only include zeros, then as you change other criteria you'll be able to see the row count in the bottom of the window, e.g. "3 of 11 records found". As others have pointed out in the question Twisty linked to, Excel doesn't give you much in the way of working with the filtering mechanism, so you'll probably be limited to this sort of workaround.
0
I assume you are filtering out specific names and want to see if the names have 0 in the next column.
Create a pivot table with your names and the number.
Row Labels on Name then Number
Values on Count of Number
Brian Folan
- 426