Sum Only Visible Rows in Excel: SUBTOTAL 9 vs 109 Explained

Build a six-record example and keep summaries separate

This procedure uses functions available in Excel 2016 and later desktop editions. Place the sample in A1:C7, with record identifiers in A, region in B, and numeric amounts in C. Put summary labels in E10:E13 and their formulas in F10:F13. These rows are outside the source and stay visible during the exercises. A summary beside the list in rows 2 through 7 would not be protected: filtering hides entire worksheet rows. English formulas below use comma argument separators; adapt those separators if required by your locale.

Start with filtering turned off and all source rows unhidden. The amounts are 100, 200, 30, 40, 50 and 60, totaling 480. The three amount formulas should all return 480, and the record count should be 6. If these baseline results differ, inspect the source cells and visibility before continuing.

F10: =SUM(C2:C7)
F11: =SUBTOTAL(9,C2:C7)
F12: =SUBTOTAL(109,C2:C7)
F13: =SUBTOTAL(103,A2:A7)

Exercise 1: filter North without manually hiding rows

Select A1:C7 and enable Data > Filter. In the region dropdown, leave only North selected. Do not manually hide a row in this exercise. The visible records should be S-01, S-03, S-04 and S-06. Check those identifiers before reading the total: the wrong region can produce a valid number for a different report.

The four selected amounts add to 230. F11 and F12 should both show 230, while F10 retains 480. F13 should report four nonempty record identifiers. Use the consistently populated record column for this count, rather than an occasionally blank comments field.

Exercise 2: turn filtering off, then manually hide one row

First clear the North filter, turn Data > Filter off, and unhide worksheet rows 2 through 7. Confirm the baseline again: F10=480, F11=480, F12=480 and F13=6. This reset is part of the exercise. Do not keep the North filter for the next step.

With filtering off, manually hide worksheet row 5, which contains S-04 and an amount of 40. Hide the entire worksheet row, not column C. Five records remain visible: S-01, S-02, S-03, S-05 and S-06. Their amounts total 440.

The comparison should now read F10=480, F11=480, F12=440 and F13=5. In this manual-hide-only state, 9 includes the hidden amount and 109 excludes it. Unhide row 5 to return to the baseline.

If 9 and 109 agree after you mix a filter with manual hiding, reset to this isolated test. On the checked Excel 16.0 Build 5569, North followed by hiding row 5 produced 190 for both subtotals. That observation does not establish behavior on every version; do not promise that 9 will retain a manually hidden amount while a filter is active.

Use labels that explain what the number represents

Use these summary labels: Full dataset (SUM), SUBTOTAL 9, Visible amount (109), and Visible record count (103). Add the current exercise state beside them: All rows, North filter only, or No filter; row 5 manually hidden. A label such as Filtered selection including manual hides would overstate what the mixed case has demonstrated.

The expected-results file contains three independent control states. Repeat Exercise 1 from the baseline, reset completely, then repeat Exercise 2. Keep a short record of the steps used for a real report; a screenshot does not reveal how a row became hidden. The record count measures IDs, while the amount total measures values, so use both to check the intended set.

Avoid source totals, hidden columns, and text amounts

This example summarizes a vertical amount column. Hiding column C does not express a decision to exclude records, so do not use column hiding as a substitute for a row selection. Keep the data grain clear: one row is one record and the amount is one of its fields.

Do not mix imported grand-total lines with ordinary source records. If your export includes a final line labeled Total with amount 480, that is another numeric value unless you exclude it from the range. Nested SUBTOTAL formulas have special handling, but a pasted total is just data. Start the exercise with exactly six records, and exclude export footers deliberately when adapting it.

A numeric-looking amount stored as text can also make a result too small. Inspect a known source amount with ISNUMBER and compare a few records manually. Correct the source type using a controlled import or helper conversion. Do not change the visibility formula until you know which record amounts it is actually receiving.

Make the report maintainable when new rows arrive

The sample references stop at row 7. Adding a new record at row 8 does not change those written bounds. For recurring exports, update the full source selection and all summary ranges together, or use an Excel Table and explicit structured references. Test the next appended record as part of your refresh routine rather than assuming every report range expands automatically.

The synthetic input and expected states are included for comparison. Independent arithmetic checks establish the intended record sets and totals; native Excel 16.0 Build 5569 confirmed the isolated states reported here. That is one tested environment, not a test of every supported version or menu. Verify the reset, filter, manual-hide behavior and labels in the edition used for your live report.