Work clarity
How to Sum Only Visible Rows in Google Sheets
Published by OneCell
Use =SUBTOTAL(109,B5:B7) to sum the visible rows in a Google Sheets column. It skips rows removed by a filter and rows you hide manually. If you also need the total for the entire list, keep a separate =SUM(B5:B7). Label the two results so you can tell which one to use when the view changes. Google’s SUBTOTAL reference
Put the totals above the filter
On a computer, set up a practice tab called Orders. Put Stage in A4 and Units in B4. Enter Packing and 6 in A5 and B5, Waiting and 11 in A6 and B6, and Packing and 4 in A7 and B7.
In A1, enter All listed units. In B1, enter
=SUM(B5:B7).In A2, enter Units matching the filter. In B2, enter
=SUBTOTAL(9,B5:B7).In A3, enter Visible units. In B3, enter
=SUBTOTAL(109,B5:B7).
All three cells should show 21. Keep rows 1 to 3 above the data so they stay visible when you filter it. Code 9 counts manually hidden rows that still match the filter. Code 109 skips those rows as well. Google’s function codes and filtering rules
Filter the list, then hide a row
Select A4:B7 and choose Create a filter from the Data menu. Open the filter on Stage and leave only Packing selected. B1 should stay at 21. B2 and B3 should both show 10 because the two Packing rows contain 6 and 4 units.
With that filter still active, right-click row number 7 and choose Hide row. B1 should still show 21 and B2 should still show 10. B3 should drop to 6 because row 5 is the only visible data row.
Use a practice copy if the original is shared. Other people with access can see a basic filter, and editors can change it. Google’s desktop filter instructions and row hiding instructions
Check a value change and reset the view
Click the unhide arrow where row 7 was, then change B7 from 4 to 7. With Packing still selected, expect 24 in B1 and 13 in both B2 and B3. This time the whole-list total changes too, because you edited a source value.
Choose Remove filter from the Data menu. With all rows shown, the three totals should agree at 24. If you later filter to a selection with no matching rows, the visible sum is zero even though the full list still contains 24 units. Check the filter before acting on that zero. When you add data, check that your filter and all three formulas cover the new rows.