Sumif, Countif, Averageif, maxif, minif; with dynamic column formula

[Bizza]

Hi groovy people. This is my very first thread and it may be a doozy! I hope someone with supernatural abilities in excel can solve this one!

So, all I want to do is get the average of an entire column using the AVERAGEIFS function. The column however, changes depending on what has been chosen from a dropdown list of all the column names. I just want a formula without using named ranges, or VBA, array or anything else. Is this possible?

DynamicColumnLookup.xlsx
	A
1	Date
Sheet1

Thanks in advance groovers!

 

[Bizza]

DynamicColumnLookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Apples	Bananas	Carrots	Tomatoes		Fruit/Veg Sales
2	5/08/2021	5	3	8	7		Choose Fruit/Veg	Bananas	<dropdown
3	6/08/2021	8	6	11	10		Date from	7/08/2021
4	7/08/2021	1	0	4	3		Date to	13/08/2021
5	8/08/2021	6	4	9	8		Total	23	=SUMIFS(C:C,$A:$A,">="&H3,A:A,"<="&H4)
6	9/08/2021	4	2	7	6		Average	3.3	=AVERAGEIFS(C:C,$A:$A,">="&H3,A:A,"<="&H4)
7	10/08/2021	11	9	14	13				* the column is only looking up "Bananas" column.
8	11/08/2021	8	6	11	10				* How can it lookup ANY column chosen in the dropdown?
9	12/08/2021	4	2	7	6
10	13/08/2021	1	0	4	3
11	14/08/2021	2	0	5	4
12	15/08/2021	9	7	12	11
13	Total	59	39	92	81
14	Average	5.4	3.5	8.4	7.4
Sheet1

Cell Formulas
Range	Formula
H5	H5	=SUMIFS(C:C,$A:$A,">="&H3,A:A,"<="&H4)
H6	H6	=AVERAGEIFS(C:C,$A:$A,">="&H3,A:A,"<="&H4)
B13:E13	B13	=SUM(B2:B12)
B14:E14	B14	=AVERAGE(B2:B12)

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=$B$1:$E$1

 

[Fluff]

How about

=AVERAGEIFS(INDEX(B:E,,MATCH(H2,B1:E1,0)),$A:$A,">="&H3,A:A,"<="&H4)

and

=SUMIFS(INDEX(B:E,,MATCH(H2,B1:E1,0)),$A:$A,">="&H3,A:A,"<="&H4)

 

[Bizza]

How about

=AVERAGEIFS(INDEX(B:E,,MATCH(H2,B1:E1,0)),$A:$A,">="&H3,A:A,"<="&H4)

and

=SUMIFS(INDEX(B:E,,MATCH(H2,B1:E1,0)),$A:$A,">="&H3,A:A,"<="&H4)

As simple as that! lol
And I tried the index/match but couldn't get it to work. Thanks so much

 

[Fluff]

You're welcome & thanks for the feedback.