excel

How To Sum Only Filtered Cells In Excel

Jump to

    Learning to sum only filtered cells in Excel is essential for users who need to analyze subsets of data efficiently. This skill is crucial when dealing with large datasets where you only require the total of visible cells after applying filters.

    While Excel offers functions like SUBTOTAL for this purpose, the process can be complex and time-consuming. In this guide, we'll demonstrate how to perform this task and also introduce Sourcetable, which provides a more streamlined method for summing filtered cells.

    Sum Only Visible Cells in Excel

    Using the SUBTOTAL Function

    To sum filtered cells in Excel, utilize the SUBTOTAL function, which calculates the sum of visible cells only. The function is designed to ignore any row that is not displayed because of a filter. Use the formula =SUBTOTAL(109, reference), where 109 is the function number for summing and reference is the range of cells you wish to sum. SUBTOTAL also has the capability to disregard manually hidden rows.

    Using the AGGREGATE Function

    For a more advanced approach, employ the AGGREGATE function. This function is an enhanced version of SUBTOTAL, providing additional flexibility in ignoring hidden rows, errors, or both. AGGREGATE offers 19 different calculations, including sum. Its syntax varies depending on the chosen calculation and options but for summing use =AGGREGATE(9, options, range), where 9 corresponds to the sum function. The options parameter allows you to specify what to ignore during the calculation.

    Best Practices

    When summing filtered data, ensure your formulas reference the correct cells and that you use the appropriate function number for summing. For SUBTOTAL, it is 109, and for AGGREGATE, it is 9. Always verify the range of cells included in your reference to avoid errors in your sum.

    Common Use Cases

    • excel

      Summing sales data from a specific region after applying a filter

    • excel

      Calculating the total hours worked by employees in a certain department after filtering out other departments

    • excel

      Aggregating customer orders total value during a promotional period after filtering by date range

    • excel

      Determining the total number of units sold for a particular product after excluding other products using a filter

    • excel

      Compiling the sum of expenses for a specific project while ignoring unrelated expenses through filtering

    Excel vs. Sourcetable: A Comparative Analysis

    Discover the distinct benefits of Sourcetable over traditional Excel. Sourcetable streamlines data integration, enabling users to consolidate data from various sources effortlessly.

    Excel remains a staple for data analysis but lacks Sourcetable's AI copilot. This AI feature in Sourcetable transforms user interaction, offering formula creation and template assistance through a simple chat interface.

    While Excel requires manual formula input, Sourcetable's AI copilot accelerates productivity by guiding users through complex data operations, making it a robust choice for data-intensive tasks.

    Recommended Reading

    • How to... how to total filtered data in excel
    • How to... how to keep total when filtering in excel
    • How to... excel how to sum only visible cells
    • How to... how to add filtered cells in excel
    • How to... how to select only filtered cells in excel
    • How to... how to sum highlighted cells in excel
    • How to... how to count rows in excel with filter


    Sourcetable Logo

    Try Sourcetable To Get Insights From Your Data

    Ask questions about your data in a powerful spreadsheet that your whole team knows how to use.

    Drop CSV