excel 2013 pivot table count sums greater than and less than

Question

I have a large spreadsheet in Excel 2013 with student records. Each row corresponds to one student registered in one course. The spreadsheet spans 5 years of student records. I am trying to create a pivot table that shows me the distinct count of students who have 6 or more courses as well as those with fewer than 6 courses.

One row has the following fields (and many more):
Student Number
Academic Year
Course ID
Calculated Field (as above)

The pivot table will count unique student courses (ie. John Doe in Course A). I have a calculated field in my main data that combines Academic Year (ex. 2015), student number (ex. 345987) and Course ID (ex. 195100) into a field like AY2015SN345987CS195100. So, if student 345987 takes 7 different courses in 2015, I want that to count as 7. Then I create my pivot table with rows: Academic Year and Student Number; Values are Distinct Count of Calculated Field

I have created a pivot table that calculates all distinct student courses into something like this:

Year             # of Students
+2015            501
+2014            640
+2013            465
...

If I expand my pivot table a bit more to individual student number rows, it looks like this:

Year            # of Students
2015            501
345987          7
123765          5
...

I can also create a value filter (i.e. distinct count of courses is greater than or equal to 6) applied to the Student Number, so I meet one of my criteria (ex. 6 or more) into something like this:

Year            6 or More
2015            356
2014            458
2013            290

I can also filter and get those with less than 6 courses.

However, what I really want is to show the distinct count of those students that have 6 or more courses in one year and the distinct count of those students that have less than 6 courses into a single pivot table.

The final product would look something like one of these:

Year 6 or More Less than 6 2015 356 145 2014 458 182 2013 290 175

Could you please clarify what’s the meaning of “Year: 2015 \ More: 356 \ Less 145” as it could means: 1. In year 2015 there are 356 counts of students\courses equal or above 6 and 145 counts of students\courses below 6; Or 2. In year 2015 there are 356 students taking 6 or more courses and 145 taking less than six courses. The first statement summarizes 501 records while the second includes at least 2281 records. — EEM
This would summarize 501 records. Of the 501 students, 356 took 6 or more courses and 145 took less than 6 courses. I lost my formatting somehow... In 2014, there are a total of 640 records, 458 students took 6 or more courses and 182 took less than 6 courses. — J Oke
Do you mind using working fields to perform calculations to then summarize them in a pivottable — EEM
One point about the numbers for 2015 (let’s agree on this number first). If I understand correctly in your data base you have one record or row per “Student Number \ Academic Year \ Course ID” if then you have 356 students that took 6 or more courses and 145 with less than 6 the total numbers of records for 2015 cannot be 501; it should be is at least 2,281 records i.e. (356 * 6) + (145 * 1) given by the minimum possible number of records for each group. — EEM
Correct. That would be the minimum. Duly noted. I actually have about 67000 rows. I have just simplified the example. — J Oke

EEM EEM · Accepted Answer · 2015-10-07T02:42:15

Data summarization with greater than and less than – Excel-Formula & PivotTable

Assuming the DATA is located at range B6:D176 with the following fields as described by the user (adjust range as required):

Student : Student Number

Year : Academic Year

Course ID

Key : Calculated Field

Objectives :

Classify within each year in the database the student population in two groups:

a. Students with 6 or more courses

b. Students with less than 6 courses
Summarize both groups for each year, showing for every year and each group the total of students and total of student\courses

I’m not sure that all calculations needed can be performed by a PivotTable, therefore I propose to use working fields to do the calculations then a PivotTable to summarize the results.

Working Fields :

Key : Let’s take out of this calculation the Course Id in order to have a field that contains the Year\Student combination. Enter this formula in E7 then copy till last record

=CONCATENATE("AY",$C7,"SN",$B7)

AY.SN.CS.Cnt : Count of Year\Student\Course. Enter this formula in F7 then copy till last record

=COUNTIF($E$6:$E$176,$E7)

AY.SN.Cnt : Count of Year\Student. Enter this formula in G7 then copy till last record

=1*(COUNTIF($E$6:$E7,$E7)=1)

AY.SN.CS >= 6 : Quantity of records for Year\Student with 6 or more courses. Enter this formula in H7 then copy till last record

=1*($F7>=6)

AY.SN.CS < 6 : Quantity of records for Year\Student with less than 6 courses. Enter this formula in I7 then copy till last record

=1*($F7<6)

AY.SN >= 6 : Quantity of Students with 6 or more courses in a Year. Enter this formula in J7 then copy till last record

=1*($F7>=6)*$G7

AY.SN < 6 : Quantity of Students with less than 6 courses in a Year. Enter this formula in K7 then copy till last record

=1*($F7<6)*$G7

Fig. 1

The working fields can be hidden if it’s preferable to the user

Then create a PivotTable as per figure below

Fig. 2

The PivotTable reads that in Year 2015 there are:

3 Students with 6 or more courses AY.SN >= 6 and a total of 22 courses AY.SN.CS >= 6
3 Students with less than 6 courses AY.SN < 6 and a total of 10 courses AY.SN.CS < 6

excel 2013 pivot table count sums greater than and less than

1 Answers