In this article 6 sections
- 1. SUM also calculates hidden rows
- 2. Add only visible values with SUBTOTAL
- 3. Identify the cause in order
- 4. Additional points to check
- Checklist
- References
If an Excel total differs after applying a filter, the numbers may not be missing; the total formula may not be calculating only the visible rows. This article explains the difference between SUM and SUBTOTAL and the order for distinguishing filtered rows from hidden rows.
1. SUM also calculates hidden rows
=SUM(B2:B20) adds all numbers in the range. Its result does not change even if rows are hidden by a filter. Therefore, SUM is not the right tool if you want to add only the rows visible on the screen.
2. Add only visible values with SUBTOTAL
SUBTOTAL(function_num, ref1, ...) determines the calculation method according to function_num. According to Microsoft Support documentation, 1–11 include manually hidden rows, while 101–111 exclude manually hidden rows. Rows excluded by a filter are omitted in both cases. For a total, use 9 or 109, which correspond to SUM. For example, =SUBTOTAL(109, B2:B20) calculates the total of the rows remaining after filtering and the rows that have not been manually hidden.
3. Identify the cause in order
① Check whether a filter is enabled on the Data tab. ② Check whether row numbers are skipped. Skipped numbers may be caused by filtering or manual hiding. ③ Check whether the formula in the total cell is SUM or SUBTOTAL. ④ If it is SUBTOTAL, check whether function_num is 9 or 109. If you need only the values visible on the screen, 109 is correct.
4. Additional points to check
Microsoft explains that SUBTOTAL is designed for vertically oriented ranges. In a horizontal range, the result may not change even if columns are hidden, so check the range orientation first. Also, if another SUBTOTAL is included within the range, nested values are ignored to prevent double counting. If there are multiple total cells, it is safer to leave a note beside them indicating which rows are being added.
Checklist
① Check the filter status ② Check skipped row numbers ③ Distinguish SUM from SUBTOTAL ④ If only visible values are needed, use 109; to include manually hidden rows, use 9 ⑤ Check that the range is vertical ⑥ Leave a note about the calculation range in the total cell. For the order of checks when pivot values do not change, also see the Excel PivotTable article ↗.
References
The Microsoft Support: SUBTOTAL function ↗ was checked as of November 8, 2026, Korea Standard Time. The function number table and explanation of filter behavior were used as the basis for this article. Menu names may vary by Excel version, and this article is not based on personal test results.
Sources and original article
Read the Korean original on Naver ↗
This article reflects public information available when it was written. Specifications, prices and schedules can change; check the linked official documentation.
This English edition was written and translated with AI assistance, with sources provided. For errors or additional information, please request a correction. Editorial policy