# Average column in range if it meets 2 criteria

#### matthewdrhodes

##### New Member
I'm trying to get one drop down cell that matches a particular column in a range of data & another cell that matches a different column in that range.

Example:

Page 2

 Athlete Name Type of block Effort Assignment Technique Joe Zone 1 1 .5 John Pass .75 1 .75 Alex Pass 1 .5 .25 Alex Zone .5 1 0 John Pull 1 .75 1 Alex Zone 1 1 .5

Page 1

 Athlete Name: Drop down list of names (Joe, John, Alex) Effort Assignment Technique Zone Average of Page 2 range that matches name & type here Pass Pull

I'm trying to figure out how to write an equation that goes in the red text area of Page 1 that calculates all of Page 2 range that matches athlete name in column A of page 2 to the athlete name in A2 of page 1, as well as matching all of the column B of Page 2 to the A4 cell of Page 1. So if I choose Alex as a name in Page 1, it outputs his effort grade for Zone as .75 in the red cell on Page 1.

Does that make sense?

Do I need an averageif equation? Or a index(match())?

I'm not very fluent with Excel yet & am trying to learn. If anyone can help me out or provide the resources to walk me through it, I would greatly appreciate it!

Thank you,
Matt

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

Sheet2
Pasta1
ABCDE
1Athlete NameType of blockEffortAssignmentTechnique
2JoeZone110,5
3JohnPass0,7510,75
4AlexPass10,50,25
5AlexZone0,510
6JohnPull10,751
7AlexZone110,5
Sheet2

Sheet1
Pasta1
ABCD
1Athlete Name:
2Alex
3EffortAssignmentTechnique
4Zone0,7510,25
5Pass10,50,25
Sheet1
Cell Formulas
RangeFormula

Hope this helps

M,

#### matthewdrhodes

##### New Member
Thank you Marcelo! That was really helpful! I got the average to work for all needed cells.

If I wanted it to provide a count of those averages should I use "COUNTIFS" instead of "AVERAGEIFS", I've tried but an error of expecting the 2nd argument to be in pairs is showing up?

#### Marcelo Branco

##### MrExcel MVP
If I wanted it to provide a count of those averages should I use "COUNTIFS" instead of "AVERAGEIFS"

Don't understand what you trying to do. Could you provide expected/desired results?

M.

Replies
3
Views
234
Replies
1
Views
83
Replies
1
Views
165
Replies
3
Views
146
Replies
0
Views
326

1,186,656
Messages
5,959,019
Members
438,393
Latest member
rinuis

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