#### jimblimm

##### Board Regular
i have this formula,

=INDEX(\$A\$21:\$A\$8517,MATCH(2,1/(MMULT((COUNTIF(OFFSET('MAIN DATA'!B21:B8517,(ROW(INDIRECT("1:"&ROWS('MAIN DATA'!B21:B8517)-B5+1))-1)+{-1,0,1},0,(B5+1)),B3)=(B5+1)+{-1,0,-1})+0,{1;1;1})=3),1)+(B5+1)-1))

is there a way to make the formula count the occurrence instead of indexing

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try...

=SUM(IF(FREQUENCY(IF('MAIN DATA'!B21:B8517=B3,ROW('MAIN DATA'!B21:B8517)),IF('MAIN DATA'!B21:B8517<>B3,ROW('MAIN DATA'!B21:B8517)))=(B5+1),1))

WORKS PERFECT!

on another note
is there a way to make the last value of streak be something other than identical values

using same formula

INDEX(\$A\$21:\$A\$8517,MATCH(2,1/(MMULT((COUNTIF(OFFSET('MAIN DATA'!B21:B8517,(ROW(INDIRECT("1:"&ROWS('MAIN DATA'!B21:B8517)-B5+1))-1)+{-1,0,1},0,(B5+1)),B3)=(B5+1)+{-1,0,-1})+0,{1;1;1})=3),1)+(B5+1)-1))

the b3 is the streaking value

could i add something to make the last value be somehting else
ex c3

example b5+1= 4

b3="s"

c3="t"

so the formula should look for
s
s
s
s
t

say for instance i have

a1:a15 b1:b15
1 s
2 s
3 s
4 s
5 t
6 s
7 s
8 t
9 s
10 s
11 s
12 t
13 s
14 s
15 t

i want to know how many times

1. "s" streak 4times then "t" was next value
example
s
s
s
s
t

the ref value "s" is in d1
the ref value "t" is in e1
the ref of streak, 4 or whatever number i wanna lookup is in f1

and also add the index the last time the occurrence showed up of it to h1
h1=5

i want to know how many times "s" streak 4 times then "t" was next value

Confirmed with Ctrl+Shift+Enter:

=SUMPRODUCT(--(FREQUENCY(IF(B1:B15="s", ROW(B1:B15)), IF(B1:B15="t", ROW(B1:B15)))=4))

Exacto-mondo thanx shg! u did it again

is there a way i can index instead of sum?

the counting row is a1:a15

Replies
3
Views
219
Replies
3
Views
150
Replies
5
Views
183
Replies
8
Views
232
Replies
5
Views
190

1,206,970
Messages
6,075,921
Members
446,170
Latest member
zzzz02

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