Add and count
Add only the rows you can see after filtering
=SUBTOTAL(109,B2:B5)How it works in Excel
- 109 tells SUBTOTAL to add, and to skip hidden rows.
- B2:B5 is the column.
- Use 101 for an average and 103 to count filled cells.
- Rows hidden by a filter are always skipped.
The idea in general
It only counts what is showing on the screen, so the total follows your filter.
What I assumed
- Your numbers are in B2:B5.
Check the result on your own data before relying on it.