I added the headings NUMBERS in A1 and MULTIPLES in B1. In B2 I entered this
formula:

=IF(COUNTIF(A2:A21,A2)2,1,0)

and copied it down through all rows of data. Then I created a pivot table to
sum the results. I put the NUMBERS field as a row field, and the MULTIPLES
field as the data field (summing the field). The NUMBERS row field lists the
numbers 0 - 50, and the Sum of MULTIPLES field shows me how many times each
occurred more than twice within 20 rows.

Hope this helps,

Hutch

"mpenkala" wrote:

Hi there,
I'm in need of some help... here's the problem.
In ColumnA I have a list of numbers (thousands) ranging from 0 to 50.
What I need to do is count anytime any number repeats itself 3 or more times
withing 20 rows. Example below:

ColA
1
17
50
50
12
3
22
41
44
0
14
13
50
41
12
50
7
8
9


In this example the count for the number 50 hitting 3+ times in 20 rows is
1. All others would still be at zero. Even though it's hit 4 hits, it's
within the same 20 rows, so it counts as 1. Now if the same set of numbers
occurred later on in the column, then the count for the number 50 would go to
2.

Hope this makes sense. I created a spreadsheet to track all this and it
works, but it's super big (70MB) which causes it to act very slow. I'm
hoping there's a way to scan the entire list of numbers and tell me which
number has hit 3+ times within 20 rows and how many times it's happened.

Thanks,
Matt