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

I try to generate the list based on a criteria: if the "name" on the first column = "George" (for example), display a list with subjects he/she attended and for how many hours and if he/she attended the same subjects display it only once and sum the hours.
What is colored in yellow is manually entered, just to show you what I want to accomplish.

I tried with a combination of IFERROR, INDEX, and SMALL functions but it doesn't work. Any help will be appreciated!

I highly recommend restructuring the data to 4 columns; Name, Hours per day, Subject, and Weekday.

When data is clean like this, it is much easier to work with, and you can do more with it such as build pivot tables with slicers to give a more professional report.

Please see attached.

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

It is very clean your method but I have many students and all my data is structured this way. I tried to use pivot tables with slicers but the output is not user friendly because I have to filter 30/31 columns corresponding to each day of the month. Maybe I do something wrong! Thank you!

Unfortunately Excel 2016. There are some functions like XLOOKUP which are not available in this version, that I read that are very handy for my table.

Cell F14 formula , Drag down

Formula:

Please Login or Register  to view this content.

Thank you. My problem is with cells E14,E15, E16, E17 and so on.... I want them (subjects) to be displayed from the table based on the name of the student. In my sheet they were entered manually and it's time consuming.

Might be a neater way (certainly is in 365) but this works...

E14, copied down to E18:

=IFERROR(INDEX(Disciplines,AGGREGATE(15,6,IF(ISNUMBER(MATCH(Disciplines,INDEX($B$3:$K$10,MATCH($D$14,name,0),{2;4;6;8;10}),0)),ROW(Disciplines)-MIN(ROW(Disciplines))+1,""),ROW()-ROW($E$13))),"")

F14, copied down to F18:

=IF(H14="","",SUM(IF(IF(name=$D$14,$C$3:$K$10,"")=H14,$B$3:$J$10,0)))

Edit - note the order is the same as the master list of disciplines, not the order of the days as per the example - not sure if this is important.

I changed the file by deleting the merged cells

Calculate the total hours per student and subject and the hours per day.




F14=SUMPRODUCT((ISNUMBER(MATCH($A$3:$K$10,$E14,0)))*(N(+$B$3:$L$10))*(INDEX($A$3:$K$10,,MATCH($D$13,$A$2:$K$2,0))=$D$14))

Copy down

H14=SUMPRODUCT((ISNUMBER(MATCH($A$3:$K$10,$E14,0)))*(N(+$B$3:$L$10))*(INDEX($A$3:$K$10,,MATCH($D$13,$A$2:$K$2,0))=$D$14)*($A$1:$K$1=H$13))

Copy across and down

With both formulae I get the message: "This value doesn't match the data validation restriction defined for this cell."

I try to figure why the error. Thank you!

Many thanks to the all the users who tried to assist me, especially to CARACALLA for his solution. We all have many things still to learn, me included!

Correct file.
The previous one had a wrong structure

G14=SUMPRODUCT((ISNUMBER(MATCH($A$3:$K$10,$F14,0)))*(N(+$B$3:$L$10))*(INDEX($A$3:$K$10,,MATCH($E$13,$A$2:$K$2,0))=$E$14))

Copy down


I14=SUMPRODUCT((ISNUMBER(MATCH($A$3:$K$10,$F14,0)))*(N(+$B$3:$L$10))*(INDEX($A$3:$K$10,,MATCH($E$13,$A$2:$K$2,0))=$E$14)*($A$1:$K$1=I$13))

Originally Posted by Georgeunlock

With both formulae I get the message: "This value doesn't match the data validation restriction defined for this cell."

I try to figure why the error. Thank you!

Just select cell D14, the rest are calculated automatically (Because the formula is already set)

@Georgeunlock You're Welcome. Glad to help . Thank You for the feedback and rep.