Subscribe!

Typically when you use autofilter, the task bar at the bottom of your Excel workbook will say: “5 of 10 records found”. The five is your visible rows. In some situations the records found indicator will not be displayed on the indicator. This can happen when there are many complex formulas in your filtered results. There is a workaround to this.

You can place the subtotal function in your header row in this format:

=SUBTOTAL(2,B:B)

The 2 in the subtotal() function tells Excel to count the visible items in column B.

To count visible rows with text, use =SUBTOTAL(3,B:B)-1, the “-1″ removes the header from the count.

Related posts:

  1. Delete Duplicate Rows Using Advanced Filter – Excel There are many ways to remove duplicate rows in Excel,...
  2. Excel Concatenate Function The Excel concatenate function can be used to combine multiple...
  3. Excel CELL () Function The CELL function can be used to find out information...

This entry was posted on Monday, March 2nd, 2009 at 9:27 am and is filed under Excel Quick Tricks, Functions. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Counting Visible Rows After Autofilter in Excel”

  1. jayla on July 14th, 2009 at 6:03 am

    thank you !!

Leave a Reply

You must be logged in to post a comment.