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.



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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!