How to count items in a filtered list

Here we have a list of properties.

Let’s add our own message at the top of the list that stays visible.

The first thing to do is to convert our list into anExcel table.

This will make it easier to count the rows in the list.

Note that Excel automatically names all tables.

We’ll rename this table “Properties” to make the name more meaningful.

To count total rows, we can use the functionROWS, and simply input =ROWS(Properties).

Next, we need to count the number of visible rows.

To do this, we’ll use theSUBTOTAL function.

Any rows hidden by the filter will be ignored.

For the reference, we’ll use astructured referencethat points to the first column in the table.

Now when we filter the table, we see our formulas in action.

First, we’ll enter our message as “Showing X of Y properties.”

Now we can remove our original formulas and test the message.

you could use this same approach with any filtered table.

Just see to it you reference a column where every cell contains data when you use theSUBTOTAL function.