Exceljet

Quick, clean, and to the point

Running count group by n size

Excel formula: Running count group by n size
Generic formula 
=CEILING(COUNTA(expanding_range)/size,1)
Explanation 

To creating a running count of groups of a variable size, you can use the COUNTA and CEILING function. In the example shown, C5 contains this formula:

=CEILING(COUNTA($B$5:B5)/size,1)

where "size" is the named range F4.

How this formula works

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.

Author 
Dave Bruns

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.