Complicated Countif application

[mpenkala]

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

 

[Tom Hutchins]

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]

Hey Hutch,

works great. Was hoping for one adjustment though. Currently, as you have
it set up, if I have 4 repeats within 20 the same 20 rows, it counts as 2. I
would like that to count only as 1, as it's the same 20 rows.

Either way - thanks for your help.

Matt

 

[Tom Hutchins]

Acutully, if you examine the ranges in the formulas in column B, you will see
that each one is looking at a different 20-row range. There is a lot of
overlap in the ranges, since most rows are included in twenty different
20-row ranges (the first row in one range, the second row in the next range,
the third row in the next range, etc.) I think you saw 4 repeats that were
counted in two different (but overlapping) 20-row ranges.

If you only want to include each row in a single 20-row range, that's a
different problem (find repeats in rows 1-20, then find repeats in rows
21-40, etc.) If that is what you want, let me know, and I can help you with
that.

Hutch

 

[mpenkala]

I understand what your saying Hutch. No - this is perfect for what I'm
looking for. Thanks a bunch!

Matt