• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

CMLET

New Member
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.
 

CMLET

New Member
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

Excel Ninja
upload_2018-8-11_15-55-1.png

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
 
Last edited:

CMLET

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