How to count items in a filtered list

When you're working with filtered lists, you might want to know how many items are in the list and how many items are currently visible.

In this video, we'll show you how to add a message at the top of a filtered list that displays this information.

Here we have a list of properties. If we enable "filtering," and filter the list, Excel will display the current and total record count in the status bar below. However, if we click outside the filtered list, and back again, this information is no longer displayed.

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 an Excel 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 function ROWS, and simply input =ROWS(Properties). This is a structured reference that refers only to the data rows in the Properties table, which is ideal for this use.

Next, we need to count the number of visible rows. To do this, we'll use the SUBTOTAL function. The SUBTOTAL function takes two arguments: a number, specifying the function to use, and one or more references.

The SUBTOTAL function can perform a variety of operations on data, and it has the interesting feature of being able to include or exclude hidden values. In this case, we want to count only non-blank cells that are visible, so we need the function number "103". Any rows hidden by the filter will be ignored.

For the reference, we'll use a structured reference that points to the first column in the table. The first column is named "Address," so we enter this as Properties[Address] with "Address" in square brackets.

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

To wrap things up, we just need to create a single message that includes the ROW and SUBTOTAL functions we just tested.

First, we'll enter our message as "Showing X of Y properties." Then we'll replace X and Y with the functions, using the ampersand character to concatenate, or join the functions to the text.

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

You can use this same approach with any filtered table. Just make sure you reference a column where every cell contains data when you use the SUBTOTAL function.