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")
Explanation
At the core, the OFFSET function delivers a range of 3 cells to SUM, which returns a summed result.
The arguments for OFFSET are provided as follows:
For reference we use the first cell in the data range, B5, entered as a mixed reference (column locked, row relative).
For rows , we use 0, since we don’t need to change rows.
For cols , we use the expression:
(COLUMN()-COLUMN($O$5))*3
This part of the formula figures out how many columns from the starting reference to offset. In O5, the offset is zero, in P5, the offset is 3, and so on.
Finally, height is input as 1 and width is input as 3, since in this case we always want a 1 x 3 range of cells.
Note: change 3 to the multiplier you need, shown as “n” in the generic form of the formula above.
