Applying formulas only to filtered range

[jungle125]

Hi

How does one change the formulas in row 1 so that they automatically apply only to the filtered range when for example, I want to filter by sector (column J). I´ve seen this before but have no clue if it´s a formula or a setting.

Thanks for any help!

01 Master + Observations.xlsx
	A	B	C	D	E	F	G	H	I	J
1		12	22	47.5%	39	35	32	32	32	SECTOR
2	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
3	01 AAPL.xlsx	4	5	23%	1	1	1	1	1	Information Technology
4	02 MSFT.xlsx				0	0	0	0	0	Information Technology
5	03 GOOG.xlsx	13	39	27%	1	1	1	1	1	Communication Services
6	04 AMZM.xlsx	13	19	47%	12	11	9	9	9	Consumer Discretionary
7	05 BRK-B.xlsx				0	0	0	0	0	Financials
8	06 TSLA.xlsx	9	21	46%	6	5	4	5	5	Consumer Discretionary
9	001 LAC - Copy.xlsx	13	22	24%	3	2	3	2	1	Health Care
10	08 JNJ.xlsx				0	0	0	0	0	Health Care
11	09 XOM.xlsx				0	0	0	0	0	Energy
12	10 JPM.xlsx	18	37	17%	1	0	1	1	1	Financials
13	11 NVDA.xlsx	10	25	59%	14	14	12	12	13	Information Technology
14	001 - Copy.xlsx				0	0	0	0	0	Information Technology
15	13 WMT.xlsx	16	6	47%	1	1	1	1	1	Consumer Staples
16	14 CVX.xlsx				0	0	0	0	0	Energy
17	15 LLY.xlsx				0	0	0	0	0	Health Care
Sheet2

Cell Formulas
Range	Formula
B1	B1	=AVERAGE(AAPL[Column2])
C1	C1	=AVERAGE(AAPL[Column3])
D1	D1	=SUMPRODUCT(AAPL[Column4],AAPL[Column5])/SUM(AAPL[Column5])
E1:I1	E1	=SUM(E3:E17)

 

[Fluff]

You need to use the SUBTOTAL function.