Answer

To sum only the visible cells in a filtered column, use the SUBTOTAL function.

Use =SUBTOTAL(9, Range) to sum visible cells produced by a filter. Use =SUBTOTAL(109, Range) when you need to ignore rows hidden manually in addition to filtered rows.

Explanation

1. SUBTOTAL is designed to operate on lists and tables and ignores rows hidden by Excel's AutoFilter.

2. Function number 9 tells SUBTOTAL to perform a SUM. Function number 109 tells SUBTOTAL to perform a SUM and ignore values in rows hidden manually (hidden with right-click → Hide).

3. SUBTOTAL will include values in cells that are visible after filtering and will update automatically when the filter changes.

Common formulas (copy-paste into Excel)

1. Sum visible cells in column B (rows 2–100):

   =SUBTOTAL(9, B2:B100)

2. Sum visible cells in column B and ignore rows hidden manually:

   =SUBTOTAL(109, B2:B100)

What if my data is an Excel Table?

To sum a filtered column inside an Excel Table, use the structured reference form. Example if the Table name is Table1 and the column header is Amount:

=SUBTOTAL(9, Table1[Amount])

or to ignore manual hides:

=SUBTOTAL(109, Table1[Amount])

Are there alternative methods?

Yes. Use AGGREGATE for more control or a SUMPRODUCT + SUBTOTAL combination for special cases.

1. AGGREGATE example (sums while allowing more ignore-options):

   =AGGREGATE(9, 5, B2:B100)

2. SUMPRODUCT + SUBTOTAL example (works row-by-row to sum only visible rows):

   =SUMPRODUCT(SUBTOTAL(3, OFFSET(B2, ROW(B2:B100)-ROW(B2), 0, 1)) * (B2:B100))

Note: AGGREGATE and SUMPRODUCT approaches are advanced. SUBTOTAL is simpler and recommended for ordinary filtering.

Step-by-step: Quick way to do this (numbered)

1. Select any cell inside your data range.

2. Turn on filtering: go to the Data tab and choose Filter (the menu location is the same on Windows and Mac; keyboard shortcuts differ by Excel version).

3. Apply the filter(s) you need so only the rows you want remain visible.

4. Enter the SUBTOTAL formula in a cell where you want the sum (examples above).

5. Change filters and watch the SUBTOTAL result update automatically.

Platform differences (Windows vs Mac)

1. Function behavior: SUBTOTAL and AGGREGATE work the same on Windows and Mac.

2. Menu locations: Data → Filter is the same on both platforms.

3. Keyboard shortcuts: shortcuts for toggling filters differ across Excel versions and platforms; use the menu if unsure.

Troubleshooting

1. Result is larger than expected → Some filtered rows are hidden manually; switch to SUBTOTAL(109, ...) to ignore manual hides.

2. Formula returns 0 but numbers are visible → Confirm numbers are actual numeric values, not text. Convert text numbers to numeric.

3. Summing only part of a filtered column → Ensure the Range in the SUBTOTAL formula covers exactly the column cells you intend to sum.

Best practice

1. Put the SUBTOTAL result outside the filtered data range so it does not become part of the filter.

2. Use Excel Tables (Insert → Table) and structured references to make formulas clearer and more robust.

3. Use SUBTOTAL(109, ...) when users might manually hide rows; use SUBTOTAL(9, ...) when only filter hiding is expected.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report