How to SUM Only Visible (or Filtered) Rows Using SUBTOTAL

Creating a Data Table

This step isn’t necessary to use SUBTOTAL, but it is a good way to set up filtering on a data set. Let’s create one. First, select the table, including the header row that has the data labels.

Select Data

In the Insert menu tab under the Tables section, click on the Table button to convert the data cells into a table.

Data Table

In the Create Table dialog that appears, make sure that My table has headers is checked and click OK.

CreateTable

Your data will now be structured in a table, which makes it easy to reference rows, columns, and perform filtering and sorting actions.

Data Table Output