# counting 'unique' instances of 'x' in a rolling range

#### tx12345

##### Board Regular
hi

with this formula (thanks to PGC01):

Code:
``{=MAX(COUNTIF(OFFSET(\$A\$2:\$C\$11,-1+ROW(INDIRECT("1:"&ROWS(\$A\$2:\$C\$11)-F4+1)),0,F4,COLUMNS(\$A\$2:\$C\$11)),F3))}``

we get this result using the data set shown below:

Code:
``````312	412	212
912	712	312		 Target: 120
512	120	412		 'Rolling' set of rows: 4
120	120	612 <-|	       Max Result: 5
212	512	112 <-|
512	412	912 <-|
120	120	120 <-|
912	512	912
612	412	112
312	120	812``````

As you can see, in the section of 4 rows highlighted, 120 indeed appears 5 times in total. But it only appears in two of the four rows, which would give another result of '2' instead of '5'.

i forgot to follow up on the other thread how to adjust the excellent formula shown above to not only show the aggregate total of all instances in each 'rolling' set, which it does very well, but also to count only the number of rows the target number appears in as well.

tx

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

##### MrExcel MVP
What do you mean by "period"?

#### tx12345

##### Board Regular
What do you mean by "period"?

Period refers to a group of 'rolling' rows on the way down the list. for example:

Code:
``````Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7

There are 4 sets of 4 in the above list of rows:

1-4, 2-5, 3-6, 4-7``````

tx

##### MrExcel MVP
What do you mean by "period"?

Period refers to a group of 'rolling' rows on the way down the list. for example:

Code:
``````Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7

There are 4 sets of 4 in the above list of rows:

1-4, 2-5, 3-6, 4-7``````

tx

=IF(ROWS(A2:C5)=4,IF(ROW(C5)<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:C5=\$F\$4)+0,{1;1;1})>0)+0),""),"")

Assuming that the number of columns is fixed...

F5, copied down:

=IF(ROW(INDEX(A2:\$A\$65536,\$F\$2))<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:INDEX(C2:\$C\$65536,\$F\$2)=\$F\$4)+0,{1;1;1})>0)+0),"")

#### tx12345

##### Board Regular

F5, copied down:

=IF(ROWS(A2:C5)=4,IF(ROW(C5)<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:C5=\$F\$4)+0,{1;1;1})>0)+0),""),"")

thanks again for the reply and good formula.

I think my question needs to be tuned up a bit. i was looking for a formula that will examine the entire range to find the maximum number of times (in our example) 120 appears in the sets of rows designated (4) on the way down.

Code:
``````312   412   212
912   712   312       Target: 120
512   120   412       'Rolling' set of rows: 4
120   120   612 <-|          *Max* Result: 5
212   512   112 <-|
512   412   912 <-|
120   120   120 <-|
912   512   912
612   412   112
312   120   812``````

this formula:

Code:
``{=MAX(COUNTIF(OFFSET(\$A\$2:\$C\$11,-1+ROW(INDIRECT("1:"&ROWS(\$A\$2:\$C\$11)-F4+1)),0,F4,COLUMNS(\$A\$2:\$C\$11)),F3))}``

will show the maximum number of times (in this example) '120' appears, which is great. one formula, one cell. i was just wondering if there was a way to tweak the formula so that instead of:

examining the entire range and each 'rolling' set of rows, and then finding the maximum number of times the target number appears in the designated set of rows and columns

it would:

examine the entire range and each 'rolling' set of rows, and then find the maximum number of 'rows' the target number appears in the designated set of rows only.

in the first case, the current formula results in a 5, but in the next case the result is 2, as (in this example) looking at the entire range, and looking at each set of 4 rows, the maximum number of rows '120' appears in is only 2.

basically, the 120 is essentially 'duplicating', and i want to exclude duplicating instances of the target in the respective rows under examination.

does that make sense??

thanks so much for your insights

tx

##### MrExcel MVP
F5, copied down:

=IF(ROWS(A2:C5)=4,IF(ROW(C5)<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:C5=\$F\$4)+0,{1;1;1})>0)+0),""),"")

thanks again for the reply and good formula.

I think my question needs to be tuned up a bit. i was looking for a formula that will examine the entire range to find the maximum number of times (in our example) 120 appears in the sets of rows designated (4) on the way down.

Code:
``````312   412   212
912   712   312       Target: 120
512   120   412       'Rolling' set of rows: 4
120   120   612<-|          *Max* Result: 5
212   512   112<-|
512   412   912<-|
120   120   120<-|
912   512   912
612   412   112
312   120   812``````

this formula:

Code:
``{=MAX(COUNTIF(OFFSET(\$A\$2:\$C\$11,-1+ROW(INDIRECT("1:"&ROWS(\$A\$2:\$C\$11)-F4+1)),0,F4,COLUMNS(\$A\$2:\$C\$11)),F3))}``

will show the maximum number of times (in this example) '120' appears, which is great. one formula, one cell. i was just wondering if there was a way to tweak the formula so that instead of:

examining the entire range and each 'rolling' set of rows, and then finding the maximum number of times the target number appears in the designated set of rows and columns

it would:

examine the entire range and each 'rolling' set of rows, and then find the maximum number of 'rows' the target number appears in the designated set of rows only.

in the first case, the current formula results in a 5, but in the next case the result is 2, as (in this example) looking at the entire range, and looking at each set of 4 rows, the maximum number of rows '120' appears in is only 2.

basically, the 120 is essentially 'duplicating', and i want to exclude duplicating instances of the target in the respective rows under examination.

does that make sense??

thanks so much for your insights

tx

Probably not understanding fully... What follows gives you total counts and counts with respect to a window size (period)...
aaCountOncePerRecordWithMMULT tx12345.xls
ABCDEFGH
1Total Count:434
2312412212Max223
3912712312Window:444
4512120412Target:120312512
5120120612221
6212512112212
7512412912203
8120120120202
9912512912103
10612412112102
11312120812211
12
Sheet2

Total count...

F1:

=SUM((MMULT((\$A\$2:\$C\$11=F\$4)+0,{1;1;1})>0)+0)

Max of rolling counts, which are generated (see below)...

F2:

=MAX(F5:INDEX(F5:F65536,MATCH(9.99999999999999E+307,F5:F65536)))

Generator for rolling counts...

F5:

=IF(ROW(INDEX(\$A2:\$A\$65536,\$F\$3))<=MATCH(9.99999999999999E+307,\$A:\$A),SUM((MMULT((\$A2:INDEX(\$C2:\$C\$65536,F\$3)=F\$4)+0,{1;1;1})>0)+0),"")

#### pgc01

##### MrExcel MVP

Hi again tx

I haven't been able yet to adpapt the formula to the new requirements.

However, If you want an UDF solution please try this code.

in G5

Code:
``=RollingMax(A2:C11,G3,G4)``

Hope this helps
PGC

EDIT: Corrected max value of lchar in the for loop, added 1

Code:
``````Option Base 1
Option Explicit

Function RollingMax(rR As Range, lTarget As Long, lPeriod As Long) As Long
Dim sCount As String, lRow As Long, lchar As Long, lMax As Long, lEval As Long

For lRow = 1 To rR.Rows.Count
sCount = sCount & "+" & Evaluate("=--(countif(" & rR.Rows(lRow).Address & "," & lTarget & ")>0)")
Next

For lchar = 1 To rR.Rows.Count - lPeriod + 1
lEval = Evaluate(Mid(sCount, lchar * 2, lPeriod * 2 - 1))
If lEval > lMax Then lMax = lEval
Next

RollingMax = lMax
End Function``````
Book1
ABCDEFGH
1
2312412212
3912712312Target:120
4512120412Period:5
5120120612MaxResult:3
6212512112
7512412912
8120120120
9912512912
10612412112
11312120812
12
13
Sheet3

#### tx12345

##### Board Regular
Aladin: that is a well composed formula. thank you so much for providing your insight.

PGC: you sure know a lot about excel, vba, and numbers. that is an awesome little function. i expect you should become very rich (if not already) applying that knowledge to future forecasting, or arbitrage, or whatever else you can imagine. or just make big bucks helping companies perform more efficiently.

thanks again.

tx

#### tx12345

##### Board Regular
Hope this helps
PGC

Code:
``````Option Base 1
Option Explicit

Function RollingMax(rR As Range, lTarget As Long, lPeriod As Long) As Long
Dim sCount As String, lRow As Long, lchar As Long, lMax As Long, lEval As Long

For lRow = 1 To rR.Rows.Count
sCount = sCount & "+" & Evaluate("=--(countif(" & rR.Rows(lRow).Address & "," & lTarget & ")>0)")
Next

For lchar = 1 To rR.Rows.Count - lPeriod
lEval = Evaluate(Mid(sCount, lchar * 2, lPeriod * 2 - 1))
If lEval > lMax Then lMax = lEval
Next

RollingMax = lMax
End Function``````

i have been tinkering with this code to get it to produce a minimum as well, and maybe even an average. not sure what to tweak. tried a few things, but my computer blew up tx

#### pgc01

##### MrExcel MVP
Hi again tx

I post the UDF for the MIN. As you can see it's the same logic. In the MAX you start from 0 and go up. In the MIN you start from lPeriod and go down.

Code:
``````Option Base 1
Option Explicit

Function RollingMin(rR As Range, lTarget As Long, lPeriod As Long) As Long
Dim sCount As String, lRow As Long, lchar As Long, lMin As Long, lEval As Long

For lRow = 1 To rR.Rows.Count
sCount = sCount & "+" & Evaluate("=--(countif(" & rR.Rows(lRow).Address & "," & lTarget & ")>0)")
Next

lMin = lPeriod
For lchar = 1 To rR.Rows.Count - lPeriod + 1
lEval = Evaluate(Mid(sCount, lchar * 2, lPeriod * 2 - 1))
If lEval < lMin Then lMin = lEval
Next

RollingMin = lMin
End Function``````

Hope it's clear
PGC

EDIT: Corrected max value of lchar in the for loop, added 1

Replies
6
Views
80
Replies
0
Views
59
Replies
2
Views
168
Replies
0
Views
343
Replies
6
Views
256

### Forum statistics

1,141,720
Messages
5,708,090
Members
421,545
Latest member
TWR ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back