How to reference data in a formula

[Tatum2020]

Hi,

I want to use an AVERAGEIFS formula and have a long list of criteria. Is there a way to do the calculation and reference a table that has mapped the criteria to one cell?

If I want to calculate the average sales for Part-time employees in that work in London Stores (Zone 1, zone 2, etc,.). How would I do this if I have a long list of stores in London, which are mapped in column I and J?

Thank you

 

[Eric W]

If you can use Excel 365, this will work:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Employee Group	Description	Sales			Employee Group	Description		Average
2	Full time	Zone 1	1			Full Time	Central 1		11.69231
3	Part Time	Zone 2	2			Part Time	Zone 2
4	Senior Managers	Zone 1	3
5	Full time	Zone 1	4
6	Full time	Zone 1	5
7	Full time	Zone 1	6
8	Full time	Central 1	7
9	Full time	Central 1	8
10	Full time	Central 1	9
11	Full time	Central 1	10
12	Full time	Central 1	11
13	Full time	Central 1	12
14	Full time	Central 1	13
15	Part Time	Central 1	14
16	Part Time	Central 1	15
17	Part Time	Central 1	16
18	Part Time	Central 1	17
19	Part Time	Central 1	18
20	Part Time	Zone 1	19
21	Part Time	Zone 1	20
22	Part Time	Zone 1	21
23	Part Time	Zone 1	22
24	Part Time	Zone 1	23
25	Part Time	Zone 1	24
26	Senior Managers	Zone 1	25
27
Sheet15

Cell Formulas
Range	Formula
I2	I2	=AVERAGE(FILTER(C2:C26,ISNUMBER(MATCH(A2:A26,F2:F5,0))*ISNUMBER(MATCH(B2:B26,G2:G5,0))))

Just put the values you want in the F2:F5 and G2:G5 ranges, and the I2 formula will adapt. If you can't use Excel 365, we can accomplish the same thing with a longer SUMPRODUCT formula.

 

[Tatum2020]

If you can use Excel 365, this will work:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Employee Group	Description	Sales			Employee Group	Description		Average
2	Full time	Zone 1	1			Full Time	Central 1		11.69231
3	Part Time	Zone 2	2			Part Time	Zone 2
4	Senior Managers	Zone 1	3
5	Full time	Zone 1	4
6	Full time	Zone 1	5
7	Full time	Zone 1	6
8	Full time	Central 1	7
9	Full time	Central 1	8
10	Full time	Central 1	9
11	Full time	Central 1	10
12	Full time	Central 1	11
13	Full time	Central 1	12
14	Full time	Central 1	13
15	Part Time	Central 1	14
16	Part Time	Central 1	15
17	Part Time	Central 1	16
18	Part Time	Central 1	17
19	Part Time	Central 1	18
20	Part Time	Zone 1	19
21	Part Time	Zone 1	20
22	Part Time	Zone 1	21
23	Part Time	Zone 1	22
24	Part Time	Zone 1	23
25	Part Time	Zone 1	24
26	Senior Managers	Zone 1	25
27
Sheet15

Cell Formulas
Range	Formula
I2	I2	=AVERAGE(FILTER(C2:C26,ISNUMBER(MATCH(A2:A26,F2:F5,0))*ISNUMBER(MATCH(B2:B26,G2:G5,0))))

Just put the values you want in the F2:F5 and G2:G5 ranges, and the I2 formula will adapt. If you can't use Excel 365, we can accomplish the same thing with a longer SUMPRODUCT formula.

Thank you, Eric. Would I be able to use an index match? I have a lot of stores mapped to zone 1.

 

[Eric W]

Is this more like what you want? In this version, you can only list 1 city in column G.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee Group	Description	Sales			Employee Group	City		Average		Stores
2	Full time	Zone 1	1			Full Time	London		13.36364		London	Liverpool	Manchester
3	Part Time	Zone 2	2			Part Time					Zone 1	Central 1	North 1
4	Senior Managers	Zone 1	3								Zone 2	Central 2	North 2
5	Full time	Zone 1	4									Central 3
6	Full time	Zone 1	5
7	Full time	Zone 1	6
8	Full time	Central 1	7
9	Full time	Central 1	8
10	Full time	Central 1	9
11	Full time	Central 1	10
12	Full time	Central 1	11
13	Full time	Central 1	12
14	Full time	Central 1	13
15	Part Time	Central 1	14
16	Part Time	Central 1	15
17	Part Time	Central 1	16
18	Part Time	Central 1	17
19	Part Time	Central 1	18
20	Part Time	Zone 1	19
21	Part Time	Zone 1	20
22	Part Time	Zone 1	21
23	Part Time	Zone 1	22
24	Part Time	Zone 1	23
25	Part Time	Zone 1	24
26	Senior Managers	Zone 1	25
Sheet15

Cell Formulas
Range	Formula
I2	I2	=AVERAGE(FILTER(C2:C26,ISNUMBER(MATCH(A2:A26,F2:F5,0))*ISNUMBER(MATCH(B2:B26,INDEX(K2:M10,0,MATCH(G2,K2:M2,0)),0))))

If this isn't what you want, I'm going to need a better example.

 

[Tatum2020]

Tab 1: This has my sales numbers

Tab 2: This is where the mapping is.

 

[Eric W]

First, please try to use the XL2BB add-in. See the link in my signature or the reply box. It's easy to download, install, and use. It makes it so that I don't have to retype all of your test data, which I don't have time to do.

Second, I see no relationship between your table1 and table2. Can you show a small set of data showing the relationships, as well as your input cells, and the expected results?