Exceljet

Quick, clean, and to the point

Student class enrollment with table

Excel formula: Student class enrollment with table
Generic formula 
=IF(COUNTIF(range,class),"x","")
Explanation 

To track student class enrollment when classes may appear in any order, you can create a table with additional columns, one for each class,  to mark and count enrollment. In the example shown, the formula in G6 is:

=IF(COUNTIF($C6:$F6,G$5),"x","")

Once you have classes marked, you can turn enable an autofilter and then filter on each class as needed to list enrolled students.

How this formula works

Note the purpose of this example is to how one way to "normalize" data when the order of values is random. There are many ways to approach this problem.

The formula in G6 relies on the COUNTIF function to count the presence of a given class (i.e. "math", art", etc.) in a columns C through F:

=IF(COUNTIF($C6:$F6,G$5),"x","")

Class names are pulled from row 5, and references are mixed to allow the formula to be copied across and down the table.

When COUNTIF finds a class in the range, it returns a positive number . TheIF function will evaluate any positive result as TRUE and return "x". If a class isn't found, COUNTIF will return zero and IF will return an empty string ("").

Formula to count enrollment

The formula used in row 4 to count students in each class looks like this:

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

The structured reference is added automatically in this case since all data is in a table. The equivalent formula without structured references is:

=COUNTIF(G6:G15,"x")
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.