Applying a number of filters.

 
Yesterday, I was asked by Leon van Gestel, how we can display the correct number after filtering data.

First and foremost, this will not work if you use the “COUNTIF” function.

Even though you apply as many filters as you want, Excel will always display all the rows, whether filtered or not.

What we can do is, to use the “SUBTOTAL” function.
The “SUBTOTAL” function has two arguments.
The first argument is a function-number and the second is the range where the data is stored.
The function-number displays the number of non-empty cells and the COUNTA function has the number 103.
The range in this case is, cells A2 to A10, i.e, A2: A10:

You can now place a filter for a single column.

You can also have a filter for multiple columns.
Excel will always display the correct number.
Even when you filter based on the color of data.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats