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

• 
  Summing sales data from a specific region after applying a filter
• 
  Calculating the total hours worked by employees in a certain department after filtering out other departments
• 
  Aggregating customer orders total value during a promotional period after filtering by date range
• 
  Determining the total number of units sold for a particular product after excluding other products using a filter
• 
  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.