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:


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:


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


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


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.


Now press “enter”. 

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


It’s easy, isn’t it?

Learn more Excel Formulas here.