Rank based on sum in a long list!

[Nightjester]

Hi, I have a long list of University courses by course year, each year has a number of modules with a value, this value can be 2, 1 or .5. Students study modules that have a total value of 6. What I am trying to do is identify the most popular modules in each program year. So I have ordered the list by the modules with the most students on, I then need to highlight the top modules when the value totals 6 (this could be top 6 modules with a value of one, or top 3 with a value of 2 etc.)

Course, Year, Module, Count, Value
Physics, 1, Water ,8, 2
Physics, 1, Earth, 8, 1
Physics, 1, Air, 7, 2
Physics, 1, Fire, 7, 1
Physics, 1, Metals, 3, 1
Physics, 2, Water, 7, 2 etc.........

So in this example the top 4 rows would be the ones I needed, the list would then continue with year 2 modules & I need to start the process again.

In Summary - once the sum of the value column hits 6, somehow highlight those rows or cells, then skip to the next year for that course & repeat

Any advice appreciated

 

[Andrew Poulsom]

Maybe try:

=SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),E$2:E2)<=6

 

[Nightjester]

Brilliant, Many Thanks