How to use dynamic ranges with SUBTOTAL in Excel

Dynamic ranges work seamlessly with the SUBTOTAL function in Excel. Let's explore a specific example:



Example use dynamic ranges

Automatic Recalculation of City Percentages

Create a schematic table representing a customer database, as shown below:

Customer Database

This table is mutable and has an uncertain number of rows, as entries may be added or removed during operation.

In our database, we can filter clients living in a city based on filter criteria. Cell E1 will display the percentage of clients residing in different cities.

Since the size of our customer database is uncertain, we'll use dynamic names with dynamic ranges:

  1. Select the "Formulas" tab, then "Define Name" under "Defined Names".
  2. In the "Create Name" dialog, enter "clients" in the first field. For the "Refers to" field, enter the following function:
    3. =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
    Create Name
  3. In cell E1, enter the formula:
    4. =SUBTOTAL(3,clients)/COUNTA(clients)
  4. Select any cell in our database range A1:C11, then choose "Data" -> "Sort & Filter" -> "Filter".
    5. Filtering
  5. Filter the data by the city "New York" or/and "San Francisco" (column B), and E1 will instantly display the percentage of clients living in that city.
Automatic Recalculation of City Percentages

download file Download

When creating the dynamic name, we used the function COUNTA($A:$A)-1. This function counts the number of filled cells in column A. However, we needed to exclude the column header, so we subtracted 1.

In cell E1, we used the formula =SUBTOTAL(3, clients). In the first parameter, we specify which function to use for our dynamic range "clients". The code parameter 3 (or 103) corresponds to the same COUNTA() function, but this time for the filtered result. It counts only non-empty cells remaining after applying the filter.

The formula in cell E1 provides accurate data if the customer database is populated correctly. For example, a customer should not be added to the database without a name, with only a last name and city, or with just a first name and last name without specifying a city. Otherwise, the value in cell E1 won't reflect reality.