Hi SamT,
Thanks for your input. Average is probably a smarter solution to obtain the same answer that I currently have, and I guess that I would have to adapt it to work dynamically.
However, it's not the correct answer. The correct answer is mentioned in column I.
My data set looks very similar to the setup that I have shown, but instead of just have 4 Main References... I might have 10 - 20 Main References, and between 1 to 30 splits for each (which is what I'm trying to display on the workbook).
So this is why I'm trying to get a dynamic formula to work with this problem.
I've come up with
=IFERROR(SUM(INDEX(C:C,MATCH(G3,A:A,0))/SUMPRODUCT(--(G:G=INDEX(A:A,MATCH(G3,A:A,0))))),"")
Which gives me the same result as your:
However, it's not what I'm looking for.
I'm looking for something similar to what I currently have, but I feel like I'm missing a piece of the calculation to actually make it work correctly.
Thank You