Sum of Values in Another Sheet, Using Collect

Art Schneiderheinze

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.

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)

.

Combined:

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