Explanation
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 . 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:
=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")
Related formulas
Course completion status summary
Course completion summary with criteria
Related functions
IF Function
The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...
COUNTIF Function
The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...
