Average column in range if it meets 2 criteria

[matthewdrhodes]

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

 

[Marcelo Branco]

Welcome to Mr Excel forum

Sheet2

Pasta1
	A	B	C	D	E
1	Athlete Name	Type of block	Effort	Assignment	Technique
2	Joe	Zone	1	1	0,5
3	John	Pass	0,75	1	0,75
4	Alex	Pass	1	0,5	0,25
5	Alex	Zone	0,5	1	0
6	John	Pull	1	0,75	1
7	Alex	Zone	1	1	0,5
Sheet2

Sheet1

Pasta1
	A	B	C	D
1	Athlete Name:
2	Alex
3		Effort	Assignment	Technique
4	Zone	0,75	1	0,25
5	Pass	1	0,5	0,25
6	Pull	Not Found	Not Found	Not Found
Sheet1

Cell Formulas
Range	Formula
B4:D6	B4	=IFERROR(AVERAGEIFS(INDEX(Sheet2!$C$2:$E$7,0,MATCH(B$3,Sheet2!$C$1:$E$1,0)),Sheet2!$A$2:$A$7,$A$2,Sheet2!$B$2:$B$7,$A4),"Not Found")

Hope this helps

M,

 

[matthewdrhodes]

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]

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.