In the example shown, the formula in C4 is: To supply a number to ROUNDUP, we are using this expression: 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 number 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:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
