Using a Formula to create a range

[Graemea]

Hi,


I have sample data arranged as in A1:G11 and would like to create a formula that will use the parameters in cells K1 and K2 to create a range that will be used in a function.


For example, K1 is 28/02/2018 and K2 is Profit so the range that should be returned the formula for use in whatever function I put in cell K3 should be E4:E11.


Can someone please tell me how to create such a range.


Thanks!

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	31/01/2018	31/01/2018	28/02/2018	28/02/2018	31/03/2018	31/03/2018			DATE	28/02/2018
2	DATA TYPE	Sales	Profit	Sales	Profit	Sales	Profit			DATA TYPE	Profit
3	PRODUCT
4	AAA	100	100	100	100	100	100
5	BBB	100	100	100	100	100	100
6	CCC	100	100	100	100	100	100
7	DDD	100	100	100	100	100	100
8	EEE	100	100	100	100	100	100
9	FFF	100	100	100	100	100	100
10	GGG	100	100	100	100	100	100
11	HHH	100	100	100	100	100	100
Sheet1

 

[Scott T]

You can use index to return a range. For example below the column is summed. Note this would be put into your formula not as a stand alone formulas.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	1/31/2018	1/31/2018	2/28/2018	2/28/2018	3/31/2018	3/31/2018			DATE	2/28/2018
2	DATA TYPE	Sales	Profit	Sales	Profit	Sales	Profit			DATA TYPE	Profit
3	PRODUCT										1477
4	AAA	100	100	100	100	100	100
5	BBB	100	100	100	100	100	100
6	CCC	100	100	100	100	100	100
7	DDD	100	100	100	100	100	100
8	EEE	100	100	100	100	100	100
9	FFF	100	100	100	100	100	100
10	GGG	100	100	100	100	100	100
11	HHH	100	100	100	777	100	100
Sheet6

Cell Formulas
Range	Formula
K3	{=SUM(INDEX(B4:G11,1,MATCH(K1&K2,B1:G1&B2:G2,0)):INDEX(B4:G11,COUNTA(A4:A11),MATCH(K1&K2,B1:G1&B2:G2,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Graemea]

Excellent, thanks Scott!

 

[Scott T]

You are welcome