VBA: sumifs data and calculate score base on multiple criteria in cell value

[tendosai]

it is a bit complicated. excuse me!

I have raw_report which contain teacher name and daily attendance for student on each subject. In template, i wanna make a button so that my colleague can click and generate the score of their student base on daily attendance.

1. First, it need to look "Team Name" (one team have multiple teacher)
2. Then, it will base on date of attendance
3. Once data matches based on above criteria, it will copy/paste name of student name into the column (without duplicated) as well as their teacher name.
   In sample excel, Mr. A has 3 members so in table it will also include teacher Mr. E and Mr. F 's student accordingly.
4. with all student name in, it will SUM the subject score and total score according to subject (in sample it can be easily with sumifs but not sure in VBA)

here is the sample file Sample Test

 

[mumps]

My pleasure.