Separately counting occurances of consecutive values of the same name

[mh_9_4]

EDIT: How do I attach an example workbook??

Hi, I have recently posted on a different Excel forum with no luck relating to my current issue. The workbook in question is a tracker of mixed-martial arts fights. The issue relates to tracking the current and past champions.

I am trying to fill out a table (On Sheet3!) based off of manually input data (On Sheet1!). There are 4 columns to the table on Sheet3!, each with different formula.

Workbook Structure
Sheet1!
- The fixtures and results are here
- 2 Help columns are here

Sheet2!
- A table that shows the winner, in order of every title fight. So if Tim wins 10 title fights on the trot it says Tim 10 times.

Sheet3!
- A table that shows every new champion in order. If Tim wins 10 title fights in order then loses to Tommy, it will show Tim once, then Tommy once.
- Also includes the table I am trying to fill out.

Table I am trying to fill out:
Column A - THIS WORKS
A list of all the past champions in order
=OFFSET(A$2,0,ROWS(A$14:A14))

Column B - THIS IS THE MAIN ISSUE
The number of times a champion has defended their championship (So when they win the championship doesnt count, but every win they have as champion does count)
- Current formula does not separate win streaks. If Tim defends the belt twice, then loses and a few years later he defends the belt 3 more times. It should say 2 first time, and 3 second time. Currently it adds them together and says 5 both times.
=IF(A14="","",COUNTIFS(Sheet1!$C$3:$C$13,Sheet3!A14,Sheet1!$G$3:$G$13,"N"))

Column C - DOESNT WORK (LESS OF AN ISSUE)
The person the champion beat to win the championship. If Tim beat Tommy for the title, it SHOULD say Tommy here. Currently seems to show any random name.
=IFNA(LOOKUP(2,1/(Sheet1!$C$3:$C$13=Sheet3!A14),Sheet1!$E$3:$E$13),"")

Column D - DOESNT WORK (LES OF AN ISSUE)
The person the champion lost to, to lose the championship. If Tim lost to Tommy for the Title, it would say Tommy here.
=IF(C15="","",IFNA(LOOKUP(2,1/(Sheet1!$E$3:$E$13=Sheet3!A14),Sheet1!$C$3:$C$13),""))


The main issue is column B:B and that is what the title of the post is referring to. Any help would be massively appreciated. The attached workbook also has text boxes to explain what is going on.

Thanks in advance for any help or for taking the time to read my problem,

MH_9_4

 

[Special-K99]

FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[mh_9_4]

Hi, I have tried following that guide and I cant see the addin in the list of options despite trying the available options. Is it allowed for me to post my original link to another excel forum where the workbook is? If not, I will just delete this post as I cant figure out the screenshot system and my issue is too complicated to explain in text.