Excel Guides

How to Sum Only Visible Rows After Filtering in Excel

You filter the collections worksheet to the East branch and copy the total into a follow-up email. The figure still includes the West branch. The filter changed which invoices you can see while SUM kept adding the same range.

Use SUBTOTAL with function number 109 for a sum that skips filtered-out and manually hidden rows. Check which rows you mean to include before using the visible total in a report.

The rows visible in the collection list

This sample is filtered to East. One East invoice row has also been hidden manually.

InvoiceBranchBalanceDisplay state
INV-501East$1,200Visible
INV-502West$900Filtered out
INV-503East$300Hidden manually

SUM across the three cells returns $2,400. SUBTOTAL with 9 returns $1,500. SUBTOTAL with 109 returns $1,200. The right number depends on whether the manually hidden row should count.

Reproduce the three totals in specific cells

On a blank Excel desktop worksheet, paste the following tab-separated data into A1. Enter the balances as numbers, without a currency symbol:

Invoice	Branch	Balance
INV-501	East	1200
INV-502	West	900
INV-503	East	300

Keep the following control formulas below the data rows, so hiding row 4 will not hide the controls. Put their labels in E6:E9 and the formulas in F6:F9:

CellLabelFormula
F6All invoices=SUM(C2:C4)
F7Filtered subtotal, including manually hidden rows=SUBTOTAL(9,C2:C4)
F8Visible-row subtotal=SUBTOTAL(109,C2:C4)
F9East branch balance=SUMIFS(C2:C4,B2:B4,B2)

Select only A1:C4 and turn on Data → Filter. First leave every invoice visible. Then filter Branch to East. Finally, while that filter remains active, hide worksheet row 4 using its row header. INV-503 is the manually hidden East invoice; INV-502 in row 3 is excluded by the branch filter.

Worksheet stateSUMSUBTOTAL 9SUBTOTAL 109SUMIFS East
All three rows visible$2,400$2,400$2,400$1,500
East filter only$2,400$1,500$1,500$1,500
East filter and row 4 hidden manually$2,400$1,500$1,200$1,500

The $300 gap between F7 and F8 is INV-503 at C4. Hiding that invoice does not remove its balance from East’s receivables. Use F9 for the stated East-only business rule; use F8 for the visible-row snapshot and label it accordingly. To reset the exercise, unhide row 4 and clear the branch filter.

Use the right SUBTOTAL function number

Place the formula outside the Balance data range so it doesn’t include itself. Filtered-out rows are excluded by both versions. Function 9 includes manually hidden rows, and 109 skips them.

=SUBTOTAL(109,C2:C4)

Check the scope independently

If you want East regardless of how the sheet is filtered, calculate that business rule explicitly with SUMIFS. Keep a date and filter record when exporting the visible subtotal. A screenshot alone can miss hidden rows that affect a calculation.

Trace the totals to an explicit branch list

Give the review an explicit branch, status or date rule. If workbook display state is relevant, record that state or use a separate visible-row extract.

Reconcile the all-branch balance with the East branch list and identify the exact invoice rows included under the stated rule.

Label the reported figure clearly. East outstanding balance and visible-row subtotal can legitimately differ.

Microsoft SUBTOTAL reference · Microsoft on summing by multiple conditions

The figures and identifiers in this example are synthetic.

The amounts and included-row sets above were independently calculated using Microsoft’s documented SUBTOTAL rules. Native Excel filtering, hiding and recalculation were not performed during this check, and a live Jolene.pro result could not be verified. These are expected synthetic results.

Keep this exercise’s balance data in one vertical column, with no nested subtotal rows. Formula examples use English names and comma separators; some Excel locales require semicolons. If a report omits new records even after refresh, the separate PivotTable source-range exercise checks that problem. Browse more practice material in Jolene.pro Workbook Studies.

Try this with your own spreadsheet at Jolene.pro. Follow Jolene.pro on LinkedIn for more spreadsheet guides.

1 comment

Leave a comment