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.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Give this a go...


    Total Students:

    =SUMIFS({Students}, {Course}, Course@row, {Term}, Term@row)


    Count of Listings:

    =COUNTIFS({Course}, Course@row, {Term}, Term@row)



    =COUNTIFS({Course}, Course@row, {Term}, Term@row) + " // " + SUMIFS({Students}, {Course}, Course@row, {Term}, Term@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!