5 Ways To Sum Only Filtered Cells In Excel

Summing only filtered cells in Excel can be a bit tricky, but there are several ways to achieve this. Whether you're working with a small dataset or a large spreadsheet, being able to sum only the cells that meet certain criteria can be incredibly useful. In this article, we'll explore five different methods to sum only filtered cells in Excel.

Why Sum Only Filtered Cells?

Before we dive into the methods, let's quickly discuss why you might want to sum only filtered cells. When working with large datasets, it's common to need to analyze specific subsets of data. By filtering your data, you can quickly isolate the relevant information and perform calculations on only that data. Summing only filtered cells allows you to get a total or aggregate value for only the data that meets your filter criteria.

Method 1: Using the AutoFilter Feature

One of the simplest ways to sum only filtered cells is to use Excel's built-in AutoFilter feature. To do this, follow these steps:

1. Select the range of cells you want to filter.
2. Go to the "Data" tab in the ribbon and click on "Filter".
3. Use the drop-down menus to apply filters to your data.
4. Once your data is filtered, select the cell where you want to display the sum.
5. Go to the "Formulas" tab and click on "AutoSum".
6. Choose the "Sum" function and select the filtered range of cells.
7. Press Enter to display the sum.

Method 2: Using the SUBTOTAL Function

Another way to sum only filtered cells is to use the SUBTOTAL function. This function allows you to perform calculations on only the visible cells in a range. To use the SUBTOTAL function, follow these steps:

1. Select the cell where you want to display the sum.
2. Type "=SUBTOTAL(" and select the range of cells you want to sum.
3. Choose the sum function (109) and press Enter.

The SUBTOTAL function will automatically sum only the visible cells in the range.

Method 3: Using the FILTER Function

In Excel 365 and later versions, you can use the FILTER function to sum only filtered cells. This function allows you to filter a range of cells based on specific criteria and then perform calculations on only the filtered data. To use the FILTER function, follow these steps:

1. Select the cell where you want to display the sum.
2. Type "=FILTER(" and select the range of cells you want to filter.
3. Define the filter criteria and press Enter.
4. Use the SUM function to sum the filtered range of cells.

Method 4: Using a PivotTable

Another way to sum only filtered cells is to use a PivotTable. PivotTables allow you to summarize and analyze large datasets by creating custom views of your data. To use a PivotTable to sum only filtered cells, follow these steps:

1. Select the range of cells you want to summarize.
2. Go to the "Insert" tab and click on "PivotTable".
3. Create a PivotTable and add the fields you want to summarize.
4. Use the filter options to filter the data.
5. Right-click on the field you want to sum and select "Summarize by" > "Sum".

Method 5: Using VBA Macros

Finally, you can use VBA macros to sum only filtered cells. This method requires some programming knowledge, but it can be a powerful way to automate tasks in Excel. To use VBA macros to sum only filtered cells, follow these steps:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Create a new module and paste in the following code:

Sub SumFilteredCells()
    Dim rng As Range
    Set rng = Range("A1:A10") 'Change to the range you want to sum
    Dim sum As Double
    sum = 0
    For Each cell In rng
        If cell.Visible = True Then
            sum = sum + cell.Value
        End If
    Next cell
    Range("B1").Value = sum 'Change to the cell where you want to display the sum
End Sub

3. Modify the code to suit your needs and press F5 to run the macro.

Gallery of Excel Sum Functions

Excel Sum Functions Gallery

We hope this article has helped you learn how to sum only filtered cells in Excel. Whether you're using the AutoFilter feature, the SUBTOTAL function, or VBA macros, there's a method that's right for you. Experiment with different techniques to find the one that works best for your needs. Happy calculating!