excel - Excel AVERAGEIFS looking up ONE of the criteria columns

Question

I have built a large data set and I need to see the average results given many different criteria. I've done this with the AVERAGEIFS function and it works just fine, however the more and more I add its getting really time intensive.

I'm wondering if there is a way to nest a vlookup or index match or anything like that in the AVERAGEIFS that read the criteria column heading and criteria in a cell (or 2 if they need to be separated) to be added to the AVERAGEIFS.

Here is an example of my spreadsheet:

The first 3 sets of criteria I want to stay locked.

I want it to read what the 4th criteria column and criteria should be by referencing the I11 cell. The highlighted portion in the formula bar is the part that I want to reference I11 so it reads it and knows that the 4th criteria is the 'code' column and the criteria is '>7'. I can separate this into 2 separate cells if need be.

I've tried a few combinations of VLOOKUP and INDEX MATCH but cannot get it to work.

Data as Text:

Price,Type,sub cat,Time,code,amount,Result,,
,,,,,,,,
9.95,t2,d,ac,2.18," 22,780,893 ",0.73,,T2 and D and AC
118.94,u2,d,bo,2.78," 172,110,893 ",4.07,,
57.63,t1,u,ac,7.09," 128,419,877 ",-2.16,,code
8.88,t2,d,ac,1.50," 62,634,868 ",12.72,,amount < 100 000 000
11.61,u1,u,ac,2.14," 146,982,736 ",1.07,,price >10
13.46,u3,u,ac,0.93," 17,513,672 ",-13.93,,
31.53,t1,u,ac,0.89," 47,170,877 ",1.39,,
16.34,t3,d,bo,1.07," 1,914,767,076 ",-1.42,,
111.59,u1,d,bo,0.62," 2,283,546,000 ",0.67,,
72.4,u3,d,bo,10.37," 951,541,514 ",1.13,,
34.55,u3,d,bo,0.77," 951,541,514 ",-2.52,,
42.25,t1,d,bo,1.05," 63,748,352 ",8.88,,
17.18,u3,u,ac,2.64," 140,217,257 ",4.35,,
97.66,t1,d,bo,3.45," 1,070,383,954 ",1.33,,
58.49,t2,u,bo,8.64," 151,876,559 ",-0.92,,
64.48,t2,d,ac,2.35," 291,967,334 ",3.03,,
38.4,t1,u,ac,17.05," 83,478,472 ",-4.31,,
20.87,u3,d,ac,28.92," 214,080,937 ",-2.16,,
36.53,t1,d,ac,1.43," 73,438,589 ",-2.07,,
89.16,t3,u,ac,1.41," 26,786,958 ",-1.75,,
15.84,t1,u,bo,2.90," 133,560,818 ",1.76,,
3.2,u3,u,bo,2.95," 215,677,667 ",-1.06,,
25.46,t1,d,bo,3.92," 57,148,431 ",1.89,,
40,t2,d,ac,8.00," 65,274,903 ",0.61,,
27.72,t1,u,ac,2.50," 381,400,886 ",6.46,,
29.07,u3,u,ac,2.32," 52,632,107 ",-0.78,,
173.31,t1,d,ac,3.58," 31,547,380 ",-4.92,,
18.22,u3,d,ac,0.58," 292,669,493 ",4.06,,
9.59,t1,d,bo,3.60," 266,883,020 ",3.16,,
115.22,t2,d,bo,4.51," 132,376,476 ",0.78,,
64.48,u3,d,ac,3.03," 338,360,104 ",-0.95,,
41.74,t1,u,bo,25.65," 245,766,436 ",-3.42,,
5.99,t3,u,bo,2.15," 175,054,713 ",-4.37,,

标签： excelexcel-formula

Answer 1

使用 INDEX/MATCH 返回正确的列。这将要求您将列名和条件分开：

=AVERAGEIFS(G:G,B:B,"T2",C:C,"D",D:D,"AC",INDEX(A:F,0,MATCH(I11,$A$7:$G$7,0)),J11)