How To SUM Only Visible (or Filtered) Rows Using SUBTOTAL In Excel

Home » How To Sum Only Visible Cells » How To SUM Only Visible (or Filtered) Rows Using SUBTOTAL In Excel

Maybe your like

Quick Navigation

  • 1Examine the Data Set
  • 2Creating a Data Table
  • 3Building a Total Row
  • 4Filtering Data Table Rows
  • 5Using SUBTOTAL to SUM a Filtered Table
  • 6Download the SUBTOTAL Example File

SUBTOTAL LeadExcel’s basic functions, like SUM, AVERAGE, COUNT, MIN and MAX are indispensable for harnessing the power of spreadsheets, but they don’t always work well with filters and structured reports. By default, these functions tally every cell in their ranges, regardless of whether the cells are filtered or hidden. Many times in reporting, it is useful to limit functions to only consider rows that are visible on the sheet and ignore the hidden or filtered values. SUM, AVERAGE, and the like can’t do this on their own. Fortunately, Excel has a function called SUBTOTAL that will consider only visible or filtered rows in its calculations, and it can perform all the operations mentioned and more! This tutorial will walk though the use of the SUBTOTAL to sum a filtered data table…

Examine the Data Set

First off, let’s take a look at our sample data…

Data Set

We have basic sales data by Region, Sales Manager, and Year. Ideally, we’d like to be able to filter by Region to get a total for sales in that part of the country. Usually, it is easiest to filter data when it is in a data table, so let’s create one.

Tag » How To Sum Only Visible Cells