Subtotal after filtering

[lezawang]

Hi
I have a table below, when I filter Dept column then I go to Home-->Auto Sum-->Sum, excel will insert Subtotal (9,Range) which is fine but when I select any other function except the Sum for example Average, then excel will insert the average function Average(Range) but not Subtotal(1, Range). Why only when I select Sum excel will insert Subtotal()? Thank you.

Book1
	A	B	C
1	Name	Dept	Salary
2	Mary	HR	45841
3	John	HR	56885
10	Sue	HR	72574
12			175300
Sheet1

Cell Formulas
Range	Formula
C12	C12	=SUBTOTAL(9,C2:C11)

 

[Alex Blakenburg]

Are you using an Excel Table ?
If you are you should turn on the Total Row. Table Design > Total Row
Then instead of typing a formula use the drop down arrow and pick the function you want to apply.
The functions listed will use their subtotal counterpart.