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:
Working from the inside out, the COUNTIF function is set up to count the value "blue" that appears in column B: COUNTIF ( $B$5:B5 , "blue" ) Note the left side of the range reference is locked ($B$5) and the right side is relative (B5)...
The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function, but CEILING always rounds up.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.