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.
Help Article Resources
Check out the Formula Handbook template!