# How to reference data in a formula

#### Tatum2020

##### New Member
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

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Eric W

##### MrExcel MVP
If you can use Excel 365, this will work:

Book1 (version 1).xlsb
ABCDEFGHI
1Employee GroupDescriptionSalesEmployee GroupDescriptionAverage
2Full timeZone 11Full TimeCentral 111.69231
3Part TimeZone 22Part TimeZone 2
4Senior ManagersZone 13
5Full timeZone 14
6Full timeZone 15
7Full timeZone 16
8Full timeCentral 17
9Full timeCentral 18
10Full timeCentral 19
11Full timeCentral 110
12Full timeCentral 111
13Full timeCentral 112
14Full timeCentral 113
15Part TimeCentral 114
16Part TimeCentral 115
17Part TimeCentral 116
18Part TimeCentral 117
19Part TimeCentral 118
20Part TimeZone 119
21Part TimeZone 120
22Part TimeZone 121
23Part TimeZone 122
24Part TimeZone 123
25Part TimeZone 124
26Senior ManagersZone 125
27
Sheet15
Cell Formulas
RangeFormula
I2I2=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

##### New Member
If you can use Excel 365, this will work:

Book1 (version 1).xlsb
ABCDEFGHI
1Employee GroupDescriptionSalesEmployee GroupDescriptionAverage
2Full timeZone 11Full TimeCentral 111.69231
3Part TimeZone 22Part TimeZone 2
4Senior ManagersZone 13
5Full timeZone 14
6Full timeZone 15
7Full timeZone 16
8Full timeCentral 17
9Full timeCentral 18
10Full timeCentral 19
11Full timeCentral 110
12Full timeCentral 111
13Full timeCentral 112
14Full timeCentral 113
15Part TimeCentral 114
16Part TimeCentral 115
17Part TimeCentral 116
18Part TimeCentral 117
19Part TimeCentral 118
20Part TimeZone 119
21Part TimeZone 120
22Part TimeZone 121
23Part TimeZone 122
24Part TimeZone 123
25Part TimeZone 124
26Senior ManagersZone 125
27
Sheet15
Cell Formulas
RangeFormula
I2I2=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

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

Book1 (version 1).xlsb
ABCDEFGHIJKLM
1Employee GroupDescriptionSalesEmployee GroupCityAverageStores
2Full timeZone 11Full TimeLondon13.36364LondonLiverpoolManchester
3Part TimeZone 22Part TimeZone 1Central 1North 1
4Senior ManagersZone 13Zone 2Central 2North 2
5Full timeZone 14Central 3
6Full timeZone 15
7Full timeZone 16
8Full timeCentral 17
9Full timeCentral 18
10Full timeCentral 19
11Full timeCentral 110
12Full timeCentral 111
13Full timeCentral 112
14Full timeCentral 113
15Part TimeCentral 114
16Part TimeCentral 115
17Part TimeCentral 116
18Part TimeCentral 117
19Part TimeCentral 118
20Part TimeZone 119
21Part TimeZone 120
22Part TimeZone 121
23Part TimeZone 122
24Part TimeZone 123
25Part TimeZone 124
26Senior ManagersZone 125
Sheet15
Cell Formulas
RangeFormula
I2I2=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

##### New Member
Tab 1: This has my sales numbers

Tab 2: This is where the mapping is.

#### Eric W

##### MrExcel MVP
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?

Replies
5
Views
149
Replies
9
Views
109
Replies
12
Views
451
Replies
0
Views
43
Replies
7
Views
132

1,148,280
Messages
5,745,829
Members
423,981
Latest member
ph1l

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back