Both sets (9 and 109) ignore rows hidden by a filter. Method 1: Using the SUBTOTAL Function (Formula)
If you are using an Excel Table ( Ctrl + T ), you can add a Total Row which automatically uses SUBTOTAL . Click inside your table. Go to the tab. Check Total Row .
This row will automatically update its calculation when you filter the table, only totaling visible rows. SUBTOTAL vs. SUM: Why SUBTOTAL is Better SUBTOTAL function - Microsoft Support subtotal in excel
Excel will create an outline, allowing you to click 1 , 2 , or 3 in the top-left corner to show different levels of detail. Method 3: Subtotal in Excel Tables
The SUBTOTAL function returns a subtotal for a vertical range of cells, allowing you to perform calculations like SUM, AVERAGE, COUNT, MAX, and MIN, while specifically handling hidden or filtered rows. Both sets (9 and 109) ignore rows hidden by a filter
Click on Data > Subtotal (in the Outline group). Configure Dialog Box:
The first argument ( function_num ) determines whether the formula ignores manually hidden rows. Includes manually hidden rows Ignore Hidden Ignores manually hidden rows Go to the tab
In the Total Row, click a cell and choose (or other functions) from the dropdown.