# How to count consecutive same values in a range of cells

This is a discussion on How to count consecutive same values in a range of cells within the Excel Questions forums, part of the Question Forums category; I have 100 cells (4 columns by 25 rows) the contents of which are randomly generated to contain either a ...

1. ## How to count consecutive same values in a range of cells

I have 100 cells (4 columns by 25 rows) the contents of which are randomly generated to contain either a 1, 2 or 3. The cells are recalculated regularly. I am trying to determine/record the results of the number of times that 3 consecutive 1s, 2s or 3s appear, the number of times that 4 consecutive 1s, 2s or 3s appear....all the way to 20 consecutive appearances of 1s, 2s or 3s--to the extent such a run would be generated. I have tried like to come up with something and nothing seems to work. Also, because the numbers are recalculated, the results need to be tabulated/recorded in another cell before the cells are regenerated. If you can help, I would really appreciate it. Thanks a lot.

CC

2. ## Re: How to count consecutive same values in a range of cells

Consecutive across the row or down the columns? Does it count if the last two values in 1 row match the first 2 in the next? Etc...some clarifications could help.

3. ## Re: How to count consecutive same values in a range of cells

... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

Thanks!!

-nate

4. ## Re: How to count consecutive same values in a range of cells

Originally Posted by martinee
Consecutive across the row or down the columns? Does it count if the last two values in 1 row match the first 2 in the next? Etc...some clarifications could help.
Sorry, consecutive down the columns and if the final cell in column 1 matches the first two cells in column 2 then it would be deemed to count. the numbers appear in cells d4:d28; g4:g28; i4:i28; l4:l28.

thanks

5. ## Re: How to count consecutive same values in a range of cells

Originally Posted by sky_lennard
... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

Thanks!!

-nate
Nate-

I recognize the shortcomings of excel's random number tools. I used randbetween.

I don't need the cells highlighted, I just want to tabulate the number of times I have a run of the same number, with a run defined as at least 3 in a row.

The answer to you last question is yes.

6. ## Re: How to count consecutive same values in a range of cells

Originally Posted by sky_lennard
... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

Thanks!!

-nate
Nate-

I recognize the shortcomings of excel's random number tools. I used randbetween.

I don't need the cells highlighted, I just want to tabulate the number of times I have a run of the same number, with a run defined as at least 3 in a row. Ideally, I would have a table off to the side that would have at the top as a heading 3, 4, 5, 6 ....20 and beneath each heading it would count the number of instances that such experience occurred.

The answer to you last question is yes.