XLChat

How to Sum After Filtering in Excel

AS
aspardo
3-1-2025

Excel is a powerful tool for data analysis, and one of the most common tasks users perform is filtering data to focus on specific subsets. However, after applying a filter, you might need to sum the visible cells only. In this blog post, we'll explore how to sum after filtering in Excel using different methods.

Method 1: Using the SUBTOTAL Function

The SUBTOTAL function is incredibly useful for performing calculations on filtered data. It automatically ignores rows that are hidden by a filter.

Steps to Use SUBTOTAL:

  1. Apply Your Filter: First, apply your filter to the data set. For example, you might filter a list of sales data to show only sales from a specific region.

  2. Insert the SUBTOTAL Function:

    • Click on the cell where you want the sum to appear.
    • Type =SUBTOTAL(9, range), where 9 is the function number for SUM, and range is the range of cells you want to sum.

    Example:

    =SUBTOTAL(9, A2:A100)

  3. Press Enter: The sum of the visible cells in the specified range will be displayed.

Why Use SUBTOTAL?

  • Dynamic: The result updates automatically when you change the filter.
  • Versatile: You can use different function numbers for other calculations like average, count, etc.

Method 2: Using the AGGREGATE Function

The AGGREGATE function is another powerful tool that can handle filtered data and also ignore errors and hidden rows.

Steps to Use AGGREGATE:

  1. Apply Your Filter: As with the SUBTOTAL method, start by applying your filter.

  2. Insert the AGGREGATE Function:

    • Click on the cell where you want the sum to appear.
    • Type =AGGREGATE(9, 5, range), where 9 is the function number for SUM, 5 tells Excel to ignore hidden rows and error values, and range is the range of cells you want to sum.

    Example:

    =AGGREGATE(9, 5, A2:A100)

  3. Press Enter: The sum of the visible cells in the specified range will be displayed.

Why Use AGGREGATE?

  • Flexible: It can handle more complex scenarios, such as ignoring errors.
  • Powerful: It offers a wide range of functions and options.

Method 3: Using a PivotTable

PivotTables are excellent for summarizing data and can easily handle filtered data.

Steps to Use a PivotTable:

  1. Create a PivotTable:

    • Select your data range.
    • Go to Insert > PivotTable.
    • Choose where you want the PivotTable to be placed and click OK.

  2. Add Fields to the PivotTable:

    • Drag the field you want to filter by to the Rows or Columns area.
    • Drag the field you want to sum to the Values area.

  3. Apply the Filter:

    • Click on the drop-down arrow next to the field in the Rows or Columns area.
    • Select the filter criteria you want to apply.

  4. View the Sum: The PivotTable will automatically show the sum of the filtered data.

Why Use a PivotTable?

  • Interactive: You can easily change the filter and see the updated sum.
  • Comprehensive: PivotTables can handle multiple levels of data analysis.

Conclusion

Summing after filtering in Excel can be efficiently done using the SUBTOTAL, AGGREGATE functions, or by utilizing PivotTables. Each method has its advantages, so choose the one that best fits your specific needs. Whether you're dealing with sales data, financial reports, or any other dataset, these techniques will help you quickly and accurately sum your filtered data.