To count the number of rows that are visible to the naked eye regarding particular data, you can follow the SUBTOTAL Function.

Let’s take an example to understand the same:

SUBTOTAL

As you can observe in the image above, we have mentioned item code, item id, color, size and sales of the same. 

When you are dealing with thousands of data and you have not used serial number and you want to know the total number of items, this is what you can do:

SUBTOTAL

The formula to check the no. of items or in other words the no. of visible rows in a filtered list is as follows:

=SUBTOTAL(

The following function requires the function number, so to count the no. of items we will use the COUNTA Function i.e. (…) 3

SUBTOTAL

After the same, comes the reference that is, you can select any column for example, we have selected all the item codes to know the total number of rows.

Do not add the heading or else you won’t get the exact number of items you have in your plate.

SUBTOTAL

Now press “enter”. 

The answer is “12” i.e. the total number of items are 12.

SUBTOTAL

It’s easy, isn’t it?

Learn more Excel Formulas here.