instead of index count

[jimblimm]

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

 

[Domenic]

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

 

[jimblimm]

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

 

[jimblimm]

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

answer in g1=1

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

 

[shg]

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

 

[jimblimm]

Exacto-mondo thanx shg! u did it again

 

[jimblimm]

is there a way i can index instead of sum?

the counting row is a1:a15