5 Ways To Sum Filtered Cells In Excel

When working with large datasets in Excel, it's often necessary to filter your data to focus on specific information. However, when you filter your data, the formulas you use to calculate sums and other aggregations may not work as expected. This is because Excel's SUM function ignores filtered cells by default. Fortunately, there are several ways to sum filtered cells in Excel, which we'll explore in this article.

Understanding the Problem with SUM Function

The SUM function in Excel is designed to add up a range of cells. However, when you apply filters to your data, the SUM function ignores the filtered cells, which can lead to incorrect results. This is because the SUM function only considers the visible cells in the range.

Method 1: Using the SUBTOTAL Function

One way to sum filtered cells in Excel is to use the SUBTOTAL function. The SUBTOTAL function is designed to work with filtered data and ignores hidden rows. To use the SUBTOTAL function, follow these steps:

• Select the cell where you want to display the sum.
• Type =SUBTOTAL(109, and select the range of cells you want to sum.
• Close the parenthesis and press Enter.

The SUBTOTAL function uses a code to specify the type of aggregation you want to perform. In this case, we use code 109 to specify the SUM function.

Method 2: Using the SUMIFS Function

Another way to sum filtered cells in Excel is to use the SUMIFS function. The SUMIFS function allows you to sum cells based on multiple criteria. To use the SUMIFS function, follow these steps:

• Select the cell where you want to display the sum.
• Type =SUMIFS( and select the range of cells you want to sum.
• Specify the criteria range and criteria.
• Close the parenthesis and press Enter.

The SUMIFS function is more flexible than the SUBTOTAL function and allows you to sum cells based on multiple criteria.

Method 3: Using a PivotTable

A PivotTable is a powerful tool in Excel that allows you to summarize and analyze large datasets. To sum filtered cells using a PivotTable, follow these steps:

• Select the cell range you want to sum.
• Go to the "Insert" tab and click on "PivotTable".
• Create a PivotTable and drag the field you want to sum to the "Values" area.
• Apply filters to the PivotTable to focus on the data you want to sum.

PivotTables are a great way to sum filtered cells because they allow you to easily switch between different filters and aggregations.

Method 4: Using Power Query

Power Query is a powerful data analysis tool in Excel that allows you to summarize and analyze large datasets. To sum filtered cells using Power Query, follow these steps:

• Select the cell range you want to sum.
• Go to the "Data" tab and click on "From Table/Range".
• Create a Power Query and apply filters to the data.
• Use the "Group By" function to sum the filtered cells.

Power Query is a great way to sum filtered cells because it allows you to easily apply filters and aggregations to large datasets.

Method 5: Using VBA Macro

Finally, you can also use a VBA macro to sum filtered cells in Excel. To use a VBA macro, follow these steps:

• Open the Visual Basic Editor by pressing "Alt+F11".
• Create a new module and paste the following code:

Sub SumFilteredCells()
  Dim rng As Range
  Set rng = Range("A1:A10") 'change to your range
  Dim sum As Double
  sum = 0
  For Each cell In rng
    If cell.Rows.Hidden = False Then
      sum = sum + cell.Value
    End If
  Next cell
  Range("B1") = sum 'change to your output cell
End Sub

• Run the macro by pressing "F5".

This macro uses a loop to iterate through the cells in the range and sum the values of the visible cells.

Gallery of Sum Filtered Cells in Excel

Sum Filtered Cells in Excel Image Gallery

In conclusion, summing filtered cells in Excel can be a challenge, but there are several ways to do it. Whether you use the SUBTOTAL function, SUMIFS function, PivotTable, Power Query, or VBA macro, the key is to understand the problem and choose the best solution for your needs. By following the methods outlined in this article, you can easily sum filtered cells in Excel and make your data analysis tasks more efficient.