Count in Excel

[Moiz]

Hi

I have this excel file with students name, unique id and their courses and
number of units for each course. Some students are taking one class and some
more than one. Classes are of different number of units, some classes are 3
units, some are 4 and some are 5. I need to bill these students based on
number of units they are taking each semester. How can i get a total number
of units for each student in this table.

Thanks





First Name Last Name Student Id Program Course Units Cost/Unit
John LEE ww5545 ADP MGMT 4 201.00
Jacob Welsh xd4457 ADP ENGR 3 201.00
James Martinez cd4785 MBA ITM7 4 185.00
James Schultz fd7485 Exchange ANTH01 4 201.00
CRJA01 4 201.00
ENVT02 4 201.00
John Hopkins rf7898 Exchange COMM02 4 201.00
ENGLHA 4 145.00
ENGLHA 4 145.00
Mary Aileen fd8547 Exchange PUAD01 4 224.00
PUAD01 4 224.00
John Winnet gg7485 MBA ITM7HA 4 185.00

 

[PJFry]

The simpliest thing to do would be to copy your Student ID to each of the
courses a student is taking. You can put a formula that calculates the
product of the units and cost per unit next to your Cost/Unit column. Then
use a SUMIF formula to sum the cost by Student ID.

There may be a more compact way of accomplishing the same thing using
SUMPRODUCT, but I believe that you will need the Student ID on every line,
regardless of the process.

 

[Moiz]

Thanks for your reply

I have about 1000 students, how ill i copy their id in front of their courses.

Thanks

 

[Don Guillett]

Start by looking in the help index for SUMIF. Or, google for sumproduct

 

[Bernd P]

Hello Don,

Sorry, but no. SUMPRODUCT has already been offered too often for tasks
which are too complex or which (as in this obvious case) require too
much manual (and repeating!) effort.

I would either recommend a pivot table or my UDF Sfreq:
http://www.sulprobil.com/html/sfreq.html

If there are more tasks to do I would suggest a proper database
application.

Regards,
Bernd

PS: It's about time to add SUMPRODUCT to my Excel Don'ts, I guess
http://www.sulprobil.com/html/excel_don_ts.html