I try to generate a list based on a criteria, but ...

Originally Posted by Georgeunlock

Thank you! You are right, it is easier to use sumproduct function to sum the hours. The part that I still don't figure out is how to display the list of subjects per each individual (cell E14,15 and so on).

With Office 2016 you will need to array enter this formula in E14 and copy down.

If you aren?t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=INDEX($C$3:$K$10,MATCH($D$14,name,0),INDEX(MODE.MULT(IF((FREQUENCY(IF(ISTEXT(INDEX($C$3:$K$10,MATCH($D$14,name,0),)),MATCH(INDEX($C$3:$K$10,MATCH($D$14,name,0),),INDEX($C$3:$K$10,MATCH($D$14,name,0),),0)),ROW(INDIRECT("1:"&COLUMNS($C$3:$K$10))))<>0),ROW(INDIRECT("1:"&COLUMNS($C$3:$K$10)))+{0,0})),ROWS(E$14:E14)))