• 589K Members
• 4,401 Online
• 714K Conversations
SOLVED

Highlighted
New Contributor

Average set of values if 2 criteria are met

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 Replies
Highlighted
Solution

Re: Average set of values if 2 criteria are met

@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.

Highlighted

Re: Average set of values if 2 criteria are met

@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!!

Related Conversations
Pivot table
gabriellerocha in Excel on
5 Replies
Excel If Functions