When creating a formula how do you select only visible cells

[fuadramsey]

I know you can use the "select only visible cells" option to select
group of visable cells when filtered or hidden, but this does not see
to work when I am entering a formula.
I hit the equall button and then the average button, here I can selec
cells with the mouse(on a filtered sheet), but when I hit the "selec
only visable cells" button, nothing change

 

[Guest]

This is a common filtering problem. You sum a column of numbers, then apply
a filter, so some of the cells are not visible, but the sum formula still
adds them in.

The solution is to use the SUBTOTAL() function. The name is mis-leading.
The function can:
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

and it ignores data hidden by filtering. See Excel Help.