Explanation
The core of this formula is the ROUNDUP function. The ROUNDUP function works like the ROUND function except that when rounding, the ROUNDUP function will always round the numbers 1-9 up. In this formula, we use that fact to repeat values.
To supply a number to ROUNDUP, we are using this expression:
(COLUMN()-2)/$B4
Without a reference, COLUMN generates the column number of the cell it appears in, in this case 3 for cell C4.
The number 2 is simply an offset value, to account for the fact column C is column 3. We subtract 2 to normalize back to 1.
Cell B4 holds the value that represents the number of times to “repeat” a count. We’ve locked the column reference so that the repeat value remains fixed as the formula is copied across the table.
The normalized column number is divided by the repeat value and the result is fed into ROUNDUP as the number to round. For n umber of places, we use zero, so that rounding goes to the next integer.
Once the column count is evenly divisible by the repeat value, the count advances.
Rows instead of columns
If you need to count in rows, instead of columns, just adjust the formula like so:
=ROUNDUP((ROW()-offset)/repeat,0)
Explanation
The table in B3:D11 is a log that shows courses completed by various people. If a course has been completed by a person, there will be an entry in the table with name, course, and date. For the purpose of this example, if we find and entry for a given name/course, we can assume that course is complete.
In the summary table in F3 to I7, we have the 4 names that appear in the data log in rows, and 3 courses we want to track as column headers. Note names and courses match entries in the data log exactly.
The core of the formula is the COUNTIFS function, which is configured with 2 range/criteria pairs. The first pair matches on the named range “name” (K5:K11) with criteria coming from $F4 (with column locked to allow the formula to be copied across the table). The second pair matches on the named range “course” (L5:L11) with criteria coming from G$3 (with row locked to allow the formula to be copied down the table).
The COUNTIFS function counts instances of each name and course in the log, using values in the summary table. When a name and course is found, COUNTIFS returns the number 1. When a name and course is not found, COUNTIFS returns zero.
We catch these results with the IF function, where COUNTIFS appears as the logical test. IF will evaluate any positive number as TRUE, and any zero result as FALSE, so we simply provide “x” for value if TRUE and an empty string ("") for value if false.
