Average from another sheet if row contains

[davedarave1]

Morning,

I have a (1st) sheet that I am trying to take average number of km ran by each individual in my team aday ( data on second sheet). How can I get it to look up the second sheet and only count data from the row if it contains their name. Both sheets contain their name, but are in different orders(I don't want to change the order)

Thank you in advance

 

[FormR]

Hi, welcome to the forum!

Something like this?

Excel 2013/2016
	A	B
1	Name	Avg
2	John	8.333333
3	Jim	5.5
Sheet1

Cell Formulas
Range	Formula
B2	=AVERAGEIFS(Sheet2!B:B,Sheet2!A:A,A2)

Excel 2013/2016
	A	B
1	Name	km
2	John	5
3	Fred	10
4	John	15
5	Jim	1
6	Adam	55
7	John	5
8	Jim	10
Sheet2

 

[davedarave1]

Thank you for your quick response. Sheet two goes from B to V(one cell per day). How can I integrate all of these?

 

[FormR]

Thank you for your quick response. Sheet two goes from B to V(one cell per day). How can I integrate all of these?

Does each person only have one row each on sheet 2?

 

[davedarave1]

Does each person only have one row each on sheet 2?

Yes. is it possible to have same formula for all, or will I need to adjust each row selection for each person?

 

[FormR]

is it possible to have same formula for all

Hi, yes - you can try like this..:

Excel 2013/2016
	A	B
1	Name	Avg
2	John	6.428571
3	Jim	6.333333
Sheet1

Cell Formulas
Range	Formula
B2	=AVERAGE(INDEX(Sheet2!B:V,MATCH(A2,Sheet2!A:A,0),0))

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Name	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8
2	John	5	6	8	5	9	6	9	4
3	Jim	7	10	9	1	1	9	2	9
4	Fred	4	5	5	3	3	8	9	7
5	Adam	7	7	1	5	5	7	7	4
6	Sally	10	10	7	5	10	2	7	5
Sheet2