How to sum filtered data

Answer

Summing filtered data in Excel requires using functions that ignore hidden rows created by filters. Standard SUM formulas include all cells, even those hidden by a filter. The correct approach is to use the SUBTOTAL function or AGGREGATE function.

1. Using the SUBTOTAL Function

SUBTOTAL calculates values only for visible cells after applying a filter. It can perform sums, averages, counts, and more.

Syntax:

SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num determines the calculation type.

• ref1, ref2 are the ranges to sum.

For summing visible data, use 109 as function_num:

=SUBTOTAL(109, B2:B50)

• 109 ignores hidden rows from filters.

• B2:B50 is the range of values to sum.

Example:

If column B contains sales amounts and you filter by product category, the formula will sum only the visible rows for the selected category.

2. Using the AGGREGATE Function

AGGREGATE is more flexible than SUBTOTAL and can ignore errors and hidden rows simultaneously.

Syntax:

AGGREGATE(function_num, options, array, [k])

• function_num specifies the calculation. 9 is for sum.

• options define behavior. 5 ignores hidden rows only.

• array is the data range.

Example:

=AGGREGATE(9, 5, B2:B50)

• 9 → SUM

• 5 → Ignore hidden rows

• B2:B50 → Values to sum

This formula sums only the visible rows after filtering.

3. Key Points to Remember

1. SUBTOTAL ignores rows hidden by filters automatically.

2. AGGREGATE can handle errors and hidden rows with more options.

3. Both formulas do not require manual adjustments when filters change.

4. Standard SUM includes hidden rows and will not reflect filtered results.

4. Example Scenario

Suppose you have a sales report for multiple regions in column A, and sales amounts in column B. You filter to show only the “West” region.

• Using:

  =SUM(B2:B50)

Will sum all regions, not just the “West.”

• Using:

  =SUBTOTAL(109, B2:B50)

Will sum only the visible “West” sales.

This method ensures accuracy in filtered data calculations.

You can apply these formulas to any numeric data table where filtering is required, ensuring correct totals without manual calculations.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report