Find and count name in range and calculate average of scores on different (fixed) row

[DaveFranken]

Hi everyone,

I've been looking for a proper and efficient way to solve my problem, but I just can't figure it out.

I have a table with some results where each instance is on a seperate column and details are found on different rows in that column (for example which employees were present). See the image attached, result table on the left.

Out of this table, I need to know for each employee how often they appear and what their average score is by filling in the table on the right side.

Getting the count is easy, but I just can't seem to find an efficient way of calculating the average. I tried the sumif function, but that doesnt allow me to have multiple rows in my range.

How should I approach this?
Person A as example; in cell O4 i need a formula that uses M4 as criteria and look for that in range C6:G11. Then for every match, sum the value in row 5 (score) and then take the average of all instances.

Your support in this is much appreciated! Thnx!

 

[DRSteele]

Welcome to the forum.

Depending on which version of Excel you are running, you might need to enter the array formula in ColO with CtrlShiftEnter.

Notice I had to put in sample data for testing purposes.

MrExcel posts18.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
3											workman	count	average
4											a	3	91.67%
5	80%		95%		100%						b	2	90.00%
6	a	2	a	three	b	120					c	1	80.00%
7	b	ana	e	crap	a	-3					d	0	no data
8	c	test	f	junk	f	#N/A					e	1	95.00%
9											f	2	97.50%
Sheet23

Cell Formulas
Range	Formula
N4:N9	N4	=COUNTIFS($C$6:$G$8,M4)
O4:O9	O4	=IF(N4=0,"no data",AVERAGE(IF($C$6:$G$8=M4,$C$5:$G$5)))
H8	H8	=NA()

 

[DaveFranken]

Yiihaa! Works like a charm! Thank you very much for both the welcome and the solution for my problem!

By now I've learned quite a few things about excel and vba, but the use of arrays was one of few things I never did before. Perhaps I should look into that a little more.

Cheers!

 

[DRSteele]

You're welcome.

Looking into arrays? Look no further than ExcelIsFun on YouTube. Mike wrote the book on CSE. Here are two Playlists to get you sorted.

Excel Array Formulas Beg - Advanced

Learn about the power of Array Formulas. Some of the topics covered are: 1.Basics of array formulas 2.Eliminate columns and formula and replace them with a s...

www.youtube.com

Excel Dynamic Arrays - The Power of Array Formulas Playlist of Videos

This playlist of videos contains video about the New Excel Calculation Engine that makes it easy to create any type of formula. For Array Formula, this new E...

www.youtube.com