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).
Sheet 1
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
Sheet 2
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.
BONUS QUESTION
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.
Comments

Give this a go...
Total Students:
=SUMIFS({Students}, {Course}, Course@row, {Term}, Term@row)
.
Count of Listings:
=COUNTIFS({Course}, Course@row, {Term}, Term@row)
.
Combined:
=COUNTIFS({Course}, Course@row, {Term}, Term@row) + " // " + SUMIFS({Students}, {Course}, Course@row, {Term}, Term@row)
Help Article Resources
Categories
Check out the Formula Handbook template!