Number after the application of a filter, in a test cell:

 
At the request of Tamar and some others, I have written the tip where you can view the number after filtering data, based on a criterion that you enter in a cell.

To be honest, I did not know how to do this.
So, I searched the Internet and hurray! hurray!, I found a solution.
He who seeks does find, isn’t it.

The formula we use for this is:
= SUMPRODUCT (SUBTOTAL (3, OFFSET (A2: A10, ROW (A2: A10)-ROW (A2), 0, 1)) – (A2: A10 = B13))
This was compiled by Mr Laurent Longre.
This person is unknown to me but, I must give credit where credit is due.

Let me try to explain what this person has achieved with this formula.
This is difficult to explain, but, anyways, let me try to do it.

He begins with the “SUMPRODUCT” function.
This function multiplies the same elements in the specified matrices and then adds the products. This consists of a minimum of 2 and a maximum of 30 matrices whose elements can be multiplied and then added.
In this case, this function consists of two matrices.
The first calculates the subtotal of the number. The number is determined by the function-number 3.
The second one converts, by means of the two minus signs, the values found to a 1 or a 0. The results of both of these are listed by the “SUMPRODUCT” function.

In the SUBTOTAL function, which calculates the number for us, we have the “OFFSET” function.
This function returns a reference to a range with a specified number of rows and columns of a cell or range of cells.
You have the option of specifying the number of rows and columns that should be included in the result.

This is all really hard to explain, but when the functions of SUMPRODUCT, SUBTOTAL and OFFSET are combined, it makes sense.

If you had no knowledge of this, never mind. The formula works and that is the most important thing.
If you change your data in cell B13, then the number in cell C13 has to be adjusted.
It does not matter whether it is filtered or not, because the correct number is always displayed.

For your information: This is a list of the various function numbers for the SUBTOTAL function:

Featured Courses

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