Average set of values if 2 criteria are met

[jtorrens]

I'm trying to find a way to average all values into a single cell if the column contains a specific text for each one of the students.

For example:

Student A has - 6 values for SCI.5.1.A, that need to be averaged into a single cell.

                       - 4 values for SCI.5.1.B, that need to be averaged into another single cell.

                       - 4 values for SCI.5.2.A, that need to be averaged into another single cell.

and so on...

I need to do this for each one of the students.

BTW, the number of values for each column title will increase as the year progresses.

I've been trying to use AVERAGEIFS, with the column name (i.e. SCI.5.1.A) as criteria1, and student name as criteria2 but I'm not sure how I can define the range to be averaged, as the ranges to meet criteria are different...

I've made 2 sheets, "Turn this" is where all the data is located, and "Into this" is how I would like to see it.

Thank you so much for help and time!!!

[#2 · Accepted solution]

@jtorrens A possible solution is demonstrated in the attached workbook. It dynamically determines the first and last column in your score listing. All you need to do is expand your summary as more courses are added. I've used a helper column in D to achieve this. Hide the column by pressing the group button at the top (and vice versa). Note that the blue shaded cells should remain empty to guarantee correct calculations.

[#3]

@Riny_van_Eekelen Thank you so much Riny!!

I finally finished playing around with it and your solution was exactly what I was looking for!!