Once you have classes marked, you can turn enable an autofilter and then filter on each class as needed to list enrolled students. 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: 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 . The IF 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: The structured reference is added automatically in this case since all data is in a table. The equivalent formula without structured references is:

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.