How to reference data in a formula

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
37
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?

1631721787802.png


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
Joined
Aug 18, 2015
Messages
11,685
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
Joined
Nov 25, 2020
Messages
37
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,685
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
Joined
Nov 25, 2020
Messages
37
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Tab 1: This has my sales numbers

1631724777735.png


Tab 2: This is where the mapping is.

1631724958644.png
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,685
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?
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top