To add only the visible rows in an Excel range, use the SUBTOTAL function with function number 109. For example, if the values are in B2:B100, enter =SUBTOTAL(109,B2:B100) in a cell outside that range. The result updates when you filter the list or hide and unhide rows manually.
This guide is for Excel for Microsoft 365 on Windows. The formula also works in current Excel for Mac and Excel for the web. The key distinction is that ordinary SUM includes manually hidden rows, while SUBTOTAL with 109 leaves out both manually hidden rows and rows removed by a filter.
What You’ll Need
- An Excel worksheet with the numbers you want to add in one vertical column or range.
- A blank cell outside the range for the total. Keeping it outside the data prevents the formula from including itself.
- Optional: a filter or rows you can hide to check that the result changes as expected.
Step-by-Step
- Identify the exact range to total. Find the first and last cells containing the values. For example, if your amounts begin in B2 and end in B100, the range is B2:B100. Avoid including a heading such as “Amount” or unrelated values below the list. If your records are in an Excel Table, you can select the relevant column, but for a first attempt a cell range is straightforward.
- Choose a total cell outside the data. Click a blank cell where you want the result to appear, such as B101 if your data ends at B100. It can also be in another column. Do not put the total in a cell that falls inside the range you plan to add; doing so can create a circular reference or cause the total to include itself.
- Enter SUBTOTAL with function number 109. With amounts in B2:B100, type
=SUBTOTAL(109,B2:B100)and press Enter. The first argument, 109, tells Excel to sum while ignoring rows hidden manually as well as rows excluded by a filter. The second argument is the range you identified. Replace the example range with your own cells, keeping the colon between the first and last cell. - Check the result before hiding anything. If all the values are currently visible, the result should match a regular sum of that same range. For a small example, suppose B2:B5 contain 12, 18, 25, and 40. With those four rows visible, the formula returns 95. This check helps reveal a mistyped range or a value stored as text before you rely on the visible-only total.
- Hide rows or apply a filter. To hide a row manually in Windows, select its row number, then choose Home > Format > Hide & Unhide > Hide Rows. You can also right-click the selected row number and choose Hide. To filter a list, select a cell in the data and choose Data > Filter; use the arrows in the column headings to choose which records to show. Excel for Mac has the same Hide Rows and Filter commands, though ribbon layout can vary slightly. Excel for the web also supports filtering and hiding rows through its ribbon or row context menu.
- Confirm the visible-only total changes. In the sample above, hide the row containing 18. The subtotal should become 77 because 12 + 25 + 40 = 77. If instead you filter out a record, SUBTOTAL also excludes that filtered-out row. When you unhide or show the record again, the total recalculates to include it.
- Keep the formula in place as the view changes. You do not need to re-enter the formula each time you change a filter or hide a row. SUBTOTAL recalculates based on the rows currently visible. If you add records below the range, however, edit the formula so the new cells are included, or consider converting the list to a Table and using its Total Row.
For clarity, function number 9 is also available, but it behaves differently: SUBTOTAL with 9 ignores rows removed by filtering, yet includes rows hidden manually. Use 109 when “without hidden rows” means excluding both types. The difference matters when a worksheet mixes filters with manually hidden rows or grouped sections.
Common Mistakes
- Using SUM instead of SUBTOTAL. A formula such as
=SUM(B2:B100)adds the range whether rows are visible or hidden. It is appropriate for a conventional total, not a visible-rows-only total. - Choosing 9 when you need to ignore manual hiding. SUBTOTAL with 9 leaves filtered-out rows out of the calculation, but it still counts rows hidden with Hide Rows or outline controls. Use 109 if both should be excluded.
- Placing the total inside its own range. If the formula is in B100 and its range includes B100, Excel may report a circular reference. Put the result in a separate cell and make sure the referenced range stops at the last data cell.
- Assuming hidden columns are excluded too. SUBTOTAL’s hidden-cell behavior applies to rows, not columns. Hiding a column does not remove its values from a vertical range’s subtotal. If your layout relies on hiding columns, use a different approach and verify the calculation against the columns you intend to include.
- Forgetting that “hidden” can mean different things. A filter hides records from the current view; Hide Rows and grouped outlines hide rows manually. SUBTOTAL with 109 excludes both, while function number 9 excludes only filtered-out records.
- Including a heading or an unintended section. Excel usually ignores text in a sum, but an oversized range can still include other numeric cells. Select and verify the intended data range rather than assuming an entire column is safe.
Tips
- Use an Excel Table for a growing list. Select the data and choose Insert > Table, confirming that the table has headers if it does. On the Table Design tab, turn on Total Row. In the total cell for the numeric column, choose Sum from its dropdown. Excel uses a SUBTOTAL-based calculation for a Table Total Row, so filtered-out records are excluded. Check its behavior if you also manually hide rows, and use an explicit 109 formula if you need that behavior to be unmistakable.
- Keep the formula easy to audit. Label the result “Visible total” or “Visible rows only” so another person does not mistake it for a total of every record. This is especially useful if the worksheet is printed or shared with filters already applied.
- Test both ways of hiding. Hide one row manually, then restore it and filter out a row. Confirm the total changes in each case. A quick test is safer than assuming the formula is using the intended range or function number.
- Remember nested subtotals are handled specially. SUBTOTAL ignores other SUBTOTAL formulas within its referenced range. This helps prevent double-counting when a list contains subtotals, but it does not make every other kind of calculation or manually typed subtotal safe to include.
- Distinguish a temporary view from deleting data. Filtering and hiding do not remove records from the workbook. The formula changes the displayed total, not the underlying values; clear the filter or unhide rows to see the full data again.
Troubleshooting
The total does not change when I hide a row. Confirm that the formula uses 109, not 9 or SUM, and that the hidden row is inside the referenced range. If the formula is in the wrong place or points to a different column, changing the row will not affect the result. Click the formula cell and inspect the range shown in the formula bar.
The total changes for filters but not for manually hidden rows. This is the usual behavior if the formula uses function number 9. Replace 9 with 109. Then test by manually hiding a row that contains a number and check that the total drops by that amount.
The total is zero or lower than expected. Verify that the range contains numeric values, not numbers stored as text. A cell that looks like 25 may be text after an import. Select a sample value and check its alignment or use Excel’s warning indicator where available; convert the source values to numbers, then recheck the subtotal. Also make sure the formula points to the correct column and includes the first and last data rows.
The formula displays as text or does not calculate. If the cell shows the characters of the formula, set its number format to General, edit the cell, and press Enter again. Check that the formula begins with an equals sign and that Show Formulas is not turned on. If the cell displays an old answer, press F9 to recalculate; if calculation is set to Manual, choose Formulas > Calculation Options > Automatic.
Rows are hidden by a grouped outline and the result seems wrong. Rows collapsed with outline controls are hidden rows, so function number 109 should ignore them when they are within the referenced range. Expand the outline once to confirm which rows are part of the group, and inspect the range for misplaced headings or totals. If a row is outside the selected range, hiding it cannot affect the result.
Frequently Asked Questions
Which Excel formula adds only visible rows?
Use SUBTOTAL with function number 109 and the range to total, for example =SUBTOTAL(109,B2:B100) when the values are in B2:B100. It excludes filtered-out rows and manually hidden rows.
Does SUBTOTAL 109 ignore filtered rows?
Yes. It ignores rows excluded by filters and rows hidden manually. When those rows become visible again, their numeric values are included in the result.
What is the difference between SUBTOTAL 9 and 109?
Both use SUM for visible data and ignore rows excluded by a filter. Number 9 includes manually hidden rows; number 109 excludes them too. Choose 109 when hidden rows should not contribute.
Can I use this with a filter in an Excel Table?
Yes. Put the SUBTOTAL formula outside the data range, or use the Table’s Total Row. Filtered-out records are excluded from a SUBTOTAL result. If you also manually hide rows, use function number 109 for a clear instruction to exclude them.
Does hiding a column remove its values from the total?
No. The hidden-row option in SUBTOTAL applies to rows, not hidden columns. If you need a result based on selected columns, define or calculate that selection separately and check the total after changing the column view.
Will the formula work in Excel for Mac or the web?
Yes. SUBTOTAL is available in Excel for Microsoft 365 on Windows, Mac, and the web. The ribbon placement can differ, but the formula syntax and its 109 behavior are the same.
Related
- How to Split Names in Excel Into Two Cells
- How to Delete an Excel File Safely on a Windows Computer
- How to Change Text to Dates in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Due Date Formula: Examples and Workday OptionsFor a basic due date in Excel, enter a start date in A2 and the number of calendar…
- Search Excel Shortcut Keys: Find Cells, Values, and FormulasIn Excel for Microsoft 365 on Windows, press Ctrl+F, enter the word, number, or phrase you want, then…
- How to Convert English to Hindi in Excel with FormulasTo convert English text in Excel to Hindi, enter the text in a cell and use Microsoft 365’s…
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- Excel Date Minus 1 Year: Formula and Leap-Year RulesTo subtract one calendar year from a date in Excel, use =EDATE(A2,-12), assuming the original date is in…
- Filter Excel Table by Multiple Criteria: Methods That WorkTo filter an Excel table by multiple criteria, open a column’s filter arrow and set its rule; rules…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.