Explanation
This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate “2nd column”, and zero as the last argument to force an exact match.
Although in this case we are mapping text values to numeric outputs, the same formula can handle text to text, or numbers to text.
Explanation
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and COUNTA returns a running count of all non-blank entries in the range.
The result of COUNTA is then divided by “size”, configured as a named range F4. Using a cell on the worksheet for group size allows the grouping to be changed at any time without editing the formula. The named range is used only for readability and convenience.
The resulting value is then processed by the CEILING function, with a significance of 1. CEILING is a rounding function that always rounds up to the next unit of significance. In this example, this causes fractional values to be rounded up to the next integer.
Handling empty cells
If the range you are counting contains blank or empty cells, you can wrap the formula inside the IF function like this:
=IF(B5<>"",CEILING(COUNTA($B$5:B5)/size,1),"")
Here, we run the counting and rounding operation described above only when the cell in column B is not blank. If it is blank, we skip the count and return an empty string ("").
