# counting instances of 'x' in a range efficiently

#### tx12345

##### Board Regular
hi

let's say i have this set of numbers

105 120 276 <--120 shows up here
212 350 274
140 370 286
310 336 276
250 331 296
270 120 376 <--120 shows up here
280 310 216
289 120 215 <--120 shows up here
280 330 216
299 250 212

3 columns wide (fixed), and x rows deep (varies), in this case 10

i am trying to find the quickest way possible to get the result, avoiding fill down formula. maybe a single cell formula can do this? a macro?

find:

1) the last time any given number appeared (the bottom number being the very last appearance), for example, '120' shows up three rows ago

2) the longest stretch of rows the number fails to appear; for example, the '120's last time to appear was 3 rows up. the time before that 2 rows between appearances, before that 5. so looking at the data the longest stretch of '120' not appearing was 5 rows.

3) the number of times any particular number might appear in any particluar number of 'rolling' rows. for example the number to target will be 276, and the 'rolling' number of rows will be set to 4

i have been doing it the long way im sure. there has to be a better way. any thoughts are very appreciated!

tx

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
tx,

=COUNTIF(OFFSET(\$B\$2,0,0,\$G\$2,3),G3) Where

G3 houses the value you need to find
G2 houses the no. of rows you need to include in your data range

If you want the columns to be dynamic replace the 3 in the above formula with a cell reference.

Hope this helps.

thanks for the reply, but i am not sure i get the formula

how does it apply to a range of cells located at, let's say, a1:c10?

thanks

1) the last time any given number appeared (the bottom number being the very last appearance), for example, '120' shows up three rows ago

Assuming that A2:C11 contains the data, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=ROWS(A2:C11)-MATCH(2,1/(MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0))>0))+1

...where G2 contains the number of interest, such as 120.

2) the longest stretch of rows the number fails to appear; for example, the '120's last time to appear was 3 rows up. the time before that 2 rows between appearances, before that 5. so looking at the data the longest stretch of '120' not appearing was 5 rows.

If we have the following data...

Code:
``````105	125	276
212	350	274
140	370	286
310	336	276
250	331	296
270	120	376	<--120 shows up here
280	310	216
289	120	215	<--120 shows up here
280	330	216
299	250	212``````

...what should the correct result be? If the correct result is 3, try the following formulas which also need to be confirmed with CONTROL+SHIFT+ENTER...

J2:

=MATCH(TRUE,MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0))>0,0)

K2:

=IF(COUNTIF(A2:C11,G2),MAX(FREQUENCY(IF(ROW(A2:C11)-ROW(A2)+1>=J2,IF(MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0))=0,ROW(A2:C11))),IF(MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0)),ROW(A2:C11))))+1,#N/A)

3) the number of times any particular number might appear in any particluar number of 'rolling' rows. for example the number to target will be 276, and the 'rolling' number of rows will be set to 4

Using the previous data posted, if the 'rolling' number of rows is set to 5, what should the correct result be? If it's 1, try...

=SUMPRODUCT(--(COUNTIF(OFFSET(A2:C11,ROW(INDIRECT("1:"&ROWS(A2:C11)/M2))*M2-M2,,M2),G2)>0))

...where M2 contains the 'rolling' number of rows, such as 5. If the number of rows that the data contains isn't evenly divisable by the set 'rolling' number of rows, in this case 5, replace...

ROWS(A2:C11)/M2

with

ROUNDUP(ROWS(A2:C11)/M2,0)

Hope this helps!

prog_9_15.xls
ABCDEFGHIJKL
1
2myNum12036xl_psychic
3RowCount10
4myRngeRef105120276LastTime3timesago
5212350274MaxStretch4
6140370286
7310336276
8250120296
9270120376
10280310216
11289120215
12280330216
13299250212
14
Sheet4

Code for Go Button:
Code:
``````Sub Stats()
'9/15/2006 by Charles R. Weber

Dim cell As Range
Dim myNum As Integer
Dim LastTime As Long, NumRows As Long, StartStretch As Long, MaxStretch As Long

myNum = Range("myNum")
NumRows = Range("RowCount")

Range("LastTime").Clear
LastTime = 0
Range("MaxStretch").Clear
MaxStretch = 0
StartStretch = 0
Range("myRnge").Interior.ColorIndex = xlNone

For Each cell In Range("myRnge")

If cell = myNum Then
cell.Interior.Color = vbYellow
If cell.Row - StartStretch > MaxStretch Then
MaxStretch = cell.Row - StartStretch
End If
StartStretch = cell.Row
LastTime = cell.Row
End If

Next cell

Range("LastTime") = Range("myRngeRef").Row + Range("RowCount") - LastTime
Range("MaxStretch") = MaxStretch
End Sub``````

myRnge is a dynamic range
=OFFSET(myRngeRef,0,0,RowCount,3)
Other range names shown on worksheet, e.g. RowCout is cell B3

Chas

the first two are perfect. thanks!

the last condition i think needs a little tweaking: ley me rephrase the question

using the same dataset:

Code:
``````105   125   276
212   350   274
140   370   286
310   336   276
250   331   296
270   120   376   <--120 shows up here
280   310   216
289   120   215   <--120 shows up here
280   330   216
299   250   212``````

with the last row the most recetn, with a rolling period of 5 rows, there are six 'sets' of five rows:

Code:
``````1
105   125   276
212   350   274
140   370   286
310   336   276
250   331   296

2
212   350   274
140   370   286
310   336   276
250   331   296
270   120   376  <--120 shows up here

3
140   370   286
310   336   276
250   331   296
270   120   376   <--120 shows up here
280   310   216

4
310   336   276
250   331   296
270   120   376   <--120 shows up here
280   310   216
289   120   215   <--120 shows up here

5
250   331   296
270   120   376   <--120 shows up here
280   310   216
289   120   215   <--120 shows up here
280   330   216

6
270   120   376   <--120 shows up here
280   310   216
289   120   215   <--120 shows up here
280   330   216
299   250   212``````

so the maximum number of time 120 appears in any of the 'rolling' sets of 5 rows is 2.

i think now you have a better idea of the question.

thanks again. the other formulas are amazing.

tx

hello chas,

sweet macro, thanks very much for the insight.

the offset is great too. i have a question about the offset:

Code:
``{=COUNTIF(OFFSET(B4:D13,0,0,G7,3),G2)}``

it does a good job of counting an aggregate total. i am also interested in the 'rolling' total, along the lines of my last post.

thanks again!

tx

the first two are perfect. thanks!

You're very welcome!

the last condition i think needs a little tweaking...

I had a feeling that that might be the case...

with the last row the most recetn, with a rolling period of 5 rows, there are six 'sets' of five rows:

Code:
``````1
105   125   276
212   350   274
140   370   286
310   336   276
250   331   296

2
212   350   274
140   370   286
310   336   276
250   331   296
270   120   376  <--120 shows up here

3
140   370   286
310   336   276
250   331   296
270   120   376   <--120 shows up here
280   310   216

4
310   336   276
250   331   296
270   120   376   <--120 shows up here
280   310   216
289   120   215   <--120 shows up here

5
250   331   296
270   120   376   <--120 shows up here
280   310   216
289   120   215   <--120 shows up here
280   330   216

6
270   120   376   <--120 shows up here
280   310   216
289   120   215   <--120 shows up here
280   330   216
299   250   212``````

so the maximum number of time 120 appears in any of the 'rolling' sets of 5 rows is 2.

A couple of questions...

1) Is the data actually laid out in this manner, or is the data listed consecutively, without the numbering for the 'rolling' sets and blank rows?

2) Will the total number of rows for the data always be evenly divisable by the number of 'rolling' sets? If not, does the counting start from the bottom? And what happens to the partial set at the top, in this case?

Hi tx

In this solution I defined some names to make it easier to read the formulas and also to move the numbers, the inputs and the outputs around. I included the definitions in A16:C20 just as documentation. You can change the range and you just have to adjust the name, the formulas do not change.

I defined
Code:
``````Names	Refer to
NLast	=Sheet1!\$B\$4
NRolling	=Sheet1!\$B\$2
Rng	=Sheet1!\$D\$4:\$F\$13
Target	=Sheet1!\$B\$1``````

The three formulas are array formulas and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

In B4:
Code:
``=ROW(Rng)+ROWS(Rng)-MAX(IF(Rng=Target,ROW(Rng)))``

In B5:
Code:
``=MAX(FREQUENCY(IF(COUNTIF(OFFSET(Rng,-1+ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)),0,1,COLUMNS(Rng)),Target)=0,ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1))),IF(COUNTIF(OFFSET(Rng,-1+ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)),0,1,COLUMNS(Rng)),Target),ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)))),NLast-1)``

In B6:
Code:
``=MAX(COUNTIF(OFFSET(Rng,-1+ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)),0,NRolling,COLUMNS(Rng)),Target))``

Hope this contributes to your solution
PGC

EDIT: Corrected the reference to Rng
Book2
ABCDEFGH
1Target120
2NRolling5
3
4NLast3105125276
5Longeststretch5212350274
6TotalNRolling2140370286
7310336276
8250331296
9270120376
10280310216
11289120215
12280330216
13299250212
14
15
16NamesReferto
17NLast=Sheet1!\$B\$4
18NRolling=Sheet1!\$B\$2
19Rng=Sheet1!\$D\$4:\$F\$13
20Target=Sheet1!\$B\$1
21
22
Sheet1

the first two are perfect. thanks!

You're very welcome!

the last condition i think needs a little tweaking...

I had a feeling that that might be the case...

...

3 columns wide, 360 rows deep

the rolling count will be a variable, anywhere from 5 to 60. so i guess there can be instances where dividing one into the other will result in a fraction.

thanks again

tx

Replies
4
Views
949
Replies
0
Views
863
Replies
4
Views
238
Replies
0
Views
557
Replies
10
Views
746

1,221,239
Messages
6,158,716
Members
451,510
Latest member
kegnazmach

### 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