< Go Back

Student class enrollment with table

In the case you need to list and keep track of student class enrollment with a table, you can use the combination of IF and COUNTIF functions in Excel. 

Excel COUNTIF formula 

=IF(COUNTIF(range, class),"x","")

Example

Figure 1. Example of Student Class Enrollment with IF and COUNTIF function

Explanation 

To trace students’ class enrollment in situations where they are not systematically arranged, create an array of the supplementary column. The supplementary range must equal the number of classes as in the first range. Spot and count enrollment for each student. In the illustration presented in G5 is

=IF(COUNTIF($C5:$F5,G$4),"x","")

Once target class have been spotted and identified within the range (C5: f5), execute auto-select to pick and enroll each of the students into the supplementary column ranges; (G5:L5)

Operationalizing this formula

Note: The essence of this illustration is to demonstrate how to order entries which are not systematically arranged. This problem can be approached in a number of ways.

The COUNTIF function provides a logical procedure to operationalize the formula as contained in cell G5. The syntax counts the classes (Bio, Phys, Math etc) in the range of column (C5:F5)

=IF(COUNTIF($C5:$F5,G$4),"x","")

To do this, structure the reference as shown in cell G5. You can now copy the syntax over the supplementary column up to L5 and down to L14. The classes are then picked from the defined cell and columns; C5:F5 into the cells and columns; G5:L5.

The COUNTIF function returns a positive value when the criterion defined is fulfilled and the IF syntax considers a positive result as TRUE only to return “x”. In the event that a criterion is not fulfilled, the COUNTIF syntax returns zero while the IF returns an empty cell (“”)

A formula to sum enrollment

The get the total count of students enrolled for each class, the formula in roll 15, cell G15 is structured as scheduled below

=COUNTIF(Table1[Eng],"x")

The defined column range sums automatically. Similarly, a case without a defined column range will be as scheduled below

=COUNTIF(G5:G14,"x")

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar