Forum Discussion
Change the layout of my data
I suggest to add an additional sheet2 and enter the unique course descriptions in range A1:DJ1 (if there are 114 descriptions altogether).
Then you can enter below formula in A2 and fill over to DJ2:
=FILTER(sheet1!$A$3:$A$153;(sheet1!$C$3:$C$153=sheet2!A1)+(sheet1!$D$3:$D$153=sheet2!A1)+(sheet1!$E$3:$E$153=sheet2!A1)+(sheet1!$F$3:$F$153=sheet2!A1)+(sheet1!$G$3:$G$153=sheet2!A1))
Formula so far covers range for course1, course2 , course3, course4 and course5 which is column C, D, E, F and G in sheet1 but can easily be adapted to the remaining columns H to Q.
However, our business school is growing exponentially. Therefore, the number of students in the program may one day outnumber the number of courses.
Ashley Fair enough! But even when you will have 1000 students and 500 courses, I guess it's unlikely that any given course will have more than 500 students. In your own example with 152 student, the most popular course had 45 participants. That's why I proposed the alternative (vertical) lay-out. Changing column names takes a few seconds and making it fully dynamic, perhaps a few minutes.
Good point! It's safe to say OliverScheurich had the best answer to my question, but Riny_van_Eekelen your addition is the Best Upgrade.