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
Figure 1. Example of Student Class Enrollment with IF and COUNTIF function
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
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)
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
The defined column range sums automatically. Similarly, a case without a defined column range will be as scheduled below