# 4 integera (with no separators) in each cell of a column range. Counting each individual interger

Copper Contributor

# 4 integera (with no separators) in each cell of a column range. Counting each individual interger

Hello-

Help! I have a column that I have manually entered (or copied in) 4 digit numbers. All cell entries are exactly four digits, 0-9, with duplicates allowed (ex. 4424). I need a formula that will examine a range of these cells and tell me how many of each integer there are, reported in separate cells. I would like it to work for and display all zeros, even leading zeros.

Column Example

0278

3094

8823

Etc.

For the column example above, in ten separate cells, I want to see that there were 2 zeros, no ones, 2 twos, 2 threes, 1 four, no fives, no six, 1 seven, 3 eights, and 1 nine. I want all zeros that are entered displayed all the time, even leading zeros.

Thank you in advance for any hints or help!  -Vinnie-

# Re: 4 integera (with no separators) in each cell of a column range. Counting each individual interge

With 365 you can do this a few different ways.

This method 'explodes'  each 4 digit number by converting each to a 1x4 array.  The result of using explode on the entire column is a matrix of numbers 4 columns wide.  TOCOL converts this matrix to a vector and then REDUCE cleans up.

``````=LET(
explode, LAMBDA(integer, MID(integer, {1, 2, 3, 4}, 1)),
number_vector, TOCOL(explode(numbers)),
unique_numbers, SORT(UNIQUE(number_vector)),
CountNumbers, LAMBDA(acc, i,
LET(
total, SUM(N(i = number_vector)),
VSTACK(acc, HSTACK(i, total))
)
),
REDUCE({"Totals", "Count"}, unique_numbers, CountNumbers)
)``````

``````=LET(
explode, LAMBDA(integer, MID(integer, {1, 2, 3, 4}, 1)),
number_vector, TOCOL(explode(numbers)),
GROUPBY(number_vector, number_vector, COUNTA)
)``````