5 Ways To Sum Filtered Cells In Excel

Summing filtered cells in Excel is a common task that can be accomplished in several ways. Whether you're dealing with a small dataset or a large one, being able to quickly and accurately sum filtered cells is a valuable skill for any Excel user. In this article, we'll explore five different methods for summing filtered cells in Excel, including using formulas, pivot tables, and more.

Understanding Filtered Cells

Before we dive into the methods for summing filtered cells, it's essential to understand what filtered cells are. In Excel, filtered cells refer to a subset of data that meets specific criteria, such as a range of values or a specific condition. When you apply a filter to a dataset, Excel hides the rows that don't meet the criteria, leaving only the relevant data visible.

Method 1: Using the SUM Formula with FILTER Function

One way to sum filtered cells in Excel is by using the SUM formula in combination with the FILTER function. The FILTER function allows you to specify a range of cells and a filter criteria, and then returns only the cells that meet the criteria. You can then use the SUM formula to add up the values in the filtered range.

Here's an example:

Assuming you have a dataset in the range A1:C10, and you want to sum the values in column C where the values in column A are greater than 10.

Formula: =SUM(FILTER(C:C, A:A>10))

This formula will sum up the values in column C only for the rows where the values in column A are greater than 10.

Method 2: Using a Pivot Table

Another way to sum filtered cells in Excel is by using a pivot table. A pivot table allows you to summarize and analyze large datasets by creating custom views of the data.

Here's an example:

Assuming you have a dataset in the range A1:C10, and you want to sum the values in column C by the values in column A.

Steps:

• Select the entire dataset (A1:C10)
• Go to the "Insert" tab in the ribbon
• Click on "PivotTable"
• Choose a cell to place the pivot table
• Drag the field from column A to the "Row Labels" area
• Drag the field from column C to the "Values" area
• Right-click on the field in the "Values" area and select "Sum"

The pivot table will now display the sum of the values in column C for each unique value in column A.

Method 3: Using the SUBTOTAL Formula

The SUBTOTAL formula is a built-in Excel formula that allows you to sum a range of cells, ignoring any hidden rows. This formula is particularly useful when working with filtered data.

Here's an example:

Assuming you have a dataset in the range A1:C10, and you want to sum the values in column C, ignoring any hidden rows.

Formula: =SUBTOTAL(109, C2:C10)

This formula will sum up the values in column C, ignoring any rows that are hidden due to filtering.

Method 4: Using the SUMIFS Formula

The SUMIFS formula is a powerful formula that allows you to sum a range of cells based on multiple criteria. This formula is particularly useful when working with large datasets.

Here's an example:

Assuming you have a dataset in the range A1:C10, and you want to sum the values in column C where the values in column A are greater than 10 and the values in column B are equal to "Sales".

Formula: =SUMIFS(C:C, A:A, ">10", B:B, "Sales")

This formula will sum up the values in column C only for the rows where the values in column A are greater than 10 and the values in column B are equal to "Sales".

Method 5: Using VBA Macro

If you're comfortable with VBA programming, you can create a macro to sum filtered cells in Excel. This method is particularly useful when working with large datasets and complex filtering criteria.

Here's an example:

Assuming you have a dataset in the range A1:C10, and you want to sum the values in column C where the values in column A are greater than 10.

Code:

Sub SumFilteredCells()
    Dim rng As Range
    Set rng = Range("A1:C10")
    Dim sum As Double
    sum = 0
    For Each cell In rng
        If cell.Value > 10 Then
            sum = sum + cell.Offset(0, 2).Value
        End If
    Next cell
    MsgBox "The sum of filtered cells is: " & sum
End Sub

This macro will sum up the values in column C only for the rows where the values in column A are greater than 10.

Excel Formula Gallery

We hope this article has helped you learn how to sum filtered cells in Excel using different methods. Whether you're a beginner or an advanced user, these methods will help you to quickly and accurately sum filtered cells in Excel. If you have any questions or need further assistance, please don't hesitate to ask.