=Match(A1,A2:4A$10600,0)

[CMLET]

A. B.

A 3
D 6
E 6
A 1
A 5
B 9
C 4
D 6
E 4
A 15
C 1
C 6
E 3
D 6
B 2
E 5
B 5
C 1

Hi Mr Excel(lent!) Need a formula to calculate occurrences between A,B,C,D,E excluding repeats: (Example) A2 cell D, A8 cell D (E,A,A,B,C) separate the rows. B2 cell 6. Exclude A B2 would equal 5. A6 cell B (C,D,E,A,C,C,E,D) separate rows B6 cell 9. Exclude the repeats (C,D,E) B6 cell would equal 5. etc, etc.

 

[bosco_yip]

Maybe........

In B1, copied down :

=IFERROR(MATCH(A1,A2:A$50,0),"")

Regards
Bosco

 

[CMLET]

Hi Bosco. Thanks. =MATCH(A1,A2:$A$10600,0) Calculates the interval between occurrences. =IFERROR(MATCH(A,A2:A$50,0),"") results were the same. A6 B, A15 B. (C,D,E,A,C,C,E,D) separate the A6,A15. exclude repeats between an occurrence B6 cell should be "5"

 

[bosco_yip]

Then,

In B1, copied down :

=IFERROR(SUMPRODUCT((A2:INDEX(A2:A$10600,MATCH(A1,A2:A$10600,0))<>"")/COUNTIF(A2:INDEX(A2:A$10600,MATCH(A1,A2:A$10600,0)),A2:INDEX(A2:A$10600,MATCH(A1,A2:A$10600,0))&"")),"")

Regards
Bosco

 

[CMLET]

Hi Bosco. Works Phenomenal! You're the Greatest! Yes, INDEX returned the value at the intersection. Thank You very much.