Count visible rows in a filtered list

An excel formula to count visible rows in a filtered list

Example

Related Functions

 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,B6:B12)

If you are hiding rows manually (i.e. right-click, Hide), and not using the auto-filter, use this version instead:

=SUBTOTAL(103,B6:B12)

0 votes. 0 / 5