Sum of Values in Another Sheet, Using Collect
I would like to find the sum of the enrollments in a particular course from one sheet (Sheet 1), and display in another sheet (Sheet 2).
There are three columns, Course, Students, Term.
Course | Students | Term
BUS 101 | 12 | Summer
BUS 101 | 33 | Summer
BUS 101 | 15 | Fall
ACT 223 | 16 | Fall
CAR 322 | 4 | Fall
I have a row for each course, which includes [Course Code], Term, and Enrollment columns. I have "BUS 101" in [Course Code] and "Summer" in Term. Thus, I want Enrollment to automatically display "45", since two of the courses in the other sheet for Summer (excluding the one for Fall), has 12 and 33, or 45, students enrolled.
I know this could be done with SUM and COLLECT, but I tried several variations, and I cannot get it to work. I get an #INVALID ARGUMENT usually. I also got 0, which was not correct.
Ideally, I would like the Enrollment cell in Sheet 2 to display "2 // 45", which says there two listings of BUS 101 in Sheet 1, and the sum of the enrollments for those two sections is 45.