How to sum filtered cells in excel

Answer

Summing filtered cells in Excel means calculating the total of only the visible rows after applying a filter. Excel excludes hidden or filtered-out rows when the correct function is used. To sum filtered cells in Excel, use the SUBTOTAL function because Excel designed it to ignore hidden or filtered-out rows.

Using SUBTOTAL with filtered data

1. Select the cell where the sum will appear.

2. Enter the formula:
   $=SUBTOTAL(9,A2:A100)$
   Function number 9 calculates the sum and ignores filtered rows.

3. Press Enter.

Using AutoSum with filtered data

1. Select the cell under the column you want to total.

2. Select Home → AutoSum.

3. Excel inserts a SUBTOTAL formula automatically.

4. Press Enter.

Windows and Mac differences

Excel uses the same SUBTOTAL function on both platforms. The ribbon paths are identical on Windows and Mac for AutoSum and for entering manual formulas.

How to sum only visible cells when rows are manually hidden

Excel interprets manually hidden rows differently from filtered rows. The AGGREGATE function helps when you want to ignore manually hidden rows.

Using AGGREGATE

1. Select the cell for the total.

2. Enter the formula:
   $=AGGREGATE(9,5,A2:A100)$
   Option 5 ignores manually hidden rows and errors.

3. Press Enter.

How to sum only visible cells in a filtered table

Excel Tables provide structured references that update automatically when the table grows.

Using SUBTOTAL inside a Table

1. Convert the range to a Table:
   Select the range → Insert → Table.

2. Add a total row:
   Table Design → Total Row.

3. Excel creates a SUBTOTAL for the selected column and updates it as you filter.

Common issues and solutions

Why is my total including hidden rows?

The total includes hidden rows because the SUM function counts every value in the range. SUM never ignores hidden rows.

Why is SUBTOTAL giving the wrong number?

1. SUBTOTAL uses visible cells only with function number 9.

2. SUBTOTAL includes manually hidden rows unless you use AGGREGATE or hide rows with a filter.

Why is AGGREGATE not working correctly?

The wrong option number is used. Option 5 ignores hidden rows. Option 3 ignores hidden rows and nested SUBTOTAL or AGGREGATE functions.

Best practices for accurate totals

1. Use filters instead of manual hiding when possible.

2. Use SUBTOTAL in tables for dynamic ranges.

3. Use AGGREGATE when the dataset mixes filtered rows and manually hidden rows.

4. Place totals outside the filtered range so the formula always remains visible.

5. Maintain consistent column data types to prevent calculation errors.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report