excel

How To Sum Only Filtered Cells In Excel

Boost your productivity with Sourcetable's AI spreadsheet assistant. Work like a spreadsheet power user and answer all your questions in seconds.


Learn more
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.

excel

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.

excel

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

sourcetable

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.



Sourcetable Logo

Work smarter, not harder

Boost your productivity with Sourcetable's AI spreadsheet assistant. Answer all your questions about spreadsheets in seconds. Try for free to get started.

Learn More
Drop CSV