Counting Number of Students Registered per Semester


New Member
Sep 14, 2010
I'm looking for an easy way to take a student's academic plan (for the next 2-4 years) and match the classes that they take with the semester they take it, and then do that for 175 other students and counting. The semesters will change depending on the students, but they all take the same classes with the exception of one elective course.

From what I've read, it seems like it would be a pivot table, but I can't wrap my head around how to make it work. All the files are based off the same template. When a student is beginning this program, their coursework until graduation is mapped out for them. Then, to get an idea of how many students will be in each course each semester, we have a separate spreadsheet that I manually add "1" for each time a semester matches that course.

I've been looking over these forums for the last few days and can't figure out if the answer is a pivot table, VBA to run a folder sweep (all the students' academic plans are in the same folder, with only that file type in there, but the names are either "student name_student#.xls" or "student name_semester.xls" so a bit inconsistent), or just some CSE formula.

For the record, I switched to an anthropology major in college when I got a B in Visual Basic with my parents' help haha. So I'm now pretty far outside my element.

Any help is greatly appreciated, and let me know if more information is needed. If it would help having the template and the current course registration table I am using, I can upload them if I can figure out how.

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.


New Member
Sep 14, 2010
After I posted this help request, I saw what a mess the folder was in. There have been updates to students' academic plans, and the new copies are also saved in the main folder. However, the template looks slightly different. It still contains the same information pertinent to my problem, but it means the cell locations would be different. In addition, since some students have more than one plan (original and updated versions) in the folder, I would only want to count the most recent.

One plan per student in the folder (the most recent), plus any that are added after the fact would need to be counted by the summary sheet.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...