Exceljet

Quick, clean, and to the point

Count with repeating values

Excel formula: Count with repeating values
Generic formula 
=ROUNDUP((COLUMN()-offset)/repeat,0)
Explanation 

To count with repeating numbers (for example: 1111,2222,3333,4444, etc.) you can use a formula based on the ROUNDUP function, with help from either ROW or COLUMN.

In the example shown, the formula in C4 is:

=ROUNDUP((COLUMN()-2)/$B4,0)

How this formula works

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 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:

=ROUNDUP((ROW()-offset)/repeat,0)

 

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.