Count visible rows in a filtered list
=SUBTOTAL(3,range)
If you want to count the number of visible items in a filtered list, you can use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter.
The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, and more. (For a full list, see the table here). What makes SUBTOTAL especially interesting and useful is that it automatically ignores items that are not visible in a filtered list or table. This makes it ideal for showing how many items are visible in a list, the subtotal of visible rows, etc.
Following the example in the image above, to count the number of non-blank rows visible when a filter is active, use:
=SUBTOTAL(3,B5:B14)
If you are hiding rows manually (i.e. right-click, Hide), and not using the auto-filter, use this version instead:
=SUBTOTAL(103,B5:B14))
