Last 5 Results

jayyemm

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
I've done some digging around and found some examples of what I'm looking for and tried to incorporate but not exactly working so I'm just looking for some more specific help. I am looking to determine the last 10 results of head to head matchups and also last 10 results overall. As you can see in the screenshot, the first 3 columns contains each player and D contains the results. Games/results are continuously added to the bottom. The "x" indicates who faced off in each game. What I'm finding confusing is finding each pIayer's last 5 results considering they might not have played the most recent game. I've tried using the following formula which contains the nested if and criteria but the formula just returns a value of 0 for Player A.

=COUNTIF(INDEX(D$2:D$1000,LARGE(IF(AND(D$2:D$1000="Player A", A$2:A$1000="<>"), ROW(D$2:E$1000)-ROW(D$2)+1),5)):D$1000,"Player A")

I would like to display it as a win-loss style similar to the records. I've been stuck here so I haven't had the opportunity to attempt at an overall last 5.

Thanks in advance for your time and help.
 

Attachments

  • Excel.JPG
    Excel.JPG
    172.9 KB · Views: 10

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
Welcome to the Board!

Try:

Book1
ABCDEFGHIJ
1Player APlayer BPlayer CResultH2HL5Row of 5th last match
2xxAA vs. B3-33-25
3xxBA vs. C4-42-312
4xxBB vs. C4-32-39
5xxA
6xxB
7xxA
8xxB
9xxC
10xxC
11xxA
12xxA
13xxB
14xxC
15xxA
16xxB
17xxC
18xxB
19xxC
20xxA
21xxC
22xxC
Sheet3
Cell Formulas
RangeFormula
H2H2=COUNTIFS(A:A,"x",B:B,"x",D:D,"A")&"-"&COUNTIFS(A:A,"x",B:B,"x",D:D,"B")
I2I2=COUNTIFS(INDEX(A:A,J2):A1000,"x",INDEX(B:B,J2):B1000,"x",INDEX(D:D,J2):D1000,"A")&"-"&COUNTIFS(INDEX(A:A,J2):A1000,"x",INDEX(B:B,J2):B1000,"x",INDEX(D:D,J2):D1000,"B")
J2J2=AGGREGATE(14,6,ROW($D$1:$D$1000)/($A$1:$A$1000="x")/($B$1:$B$1000="x"),5)
H3H3=COUNTIFS(A:A,"x",C:C,"x",D:D,"A")&"-"&COUNTIFS(A:A,"x",C:C,"x",D:D,"C")
I3I3=COUNTIFS(INDEX(A:A,J3):A1000,"x",INDEX(C:C,J3):C1000,"x",INDEX(D:D,J3):D1000,"A")&"-"&COUNTIFS(INDEX(A:A,J3):A1000,"x",INDEX(C:C,J3):C1000,"x",INDEX(D:D,J3):D1000,"C")
J3J3=AGGREGATE(14,6,ROW($D$1:$D$1000)/($A$1:$A$1000="x")/($C$1:$C$1000="x"),5)
H4H4=COUNTIFS(B:B,"x",C:C,"x",D:D,"B")&"-"&COUNTIFS(B:B,"x",C:C,"x",D:D,"C")
I4I4=COUNTIFS(INDEX(B:B,J4):B1000,"x",INDEX(C:C,J4):C1000,"x",INDEX(D:D,J4):D1000,"B")&"-"&COUNTIFS(INDEX(B:B,J4):B1000,"x",INDEX(C:C,J4):C1000,"x",INDEX(D:D,J4):D1000,"C")
J4J4=AGGREGATE(14,6,ROW($D$1:$D$1000)/($B$1:$B$1000="x")/($C$1:$C$1000="x"),5)


Note that column J is a helper column. I can incorporate it into the formula in column I, but it would make it quite long. You can hide column J if you want. Also, using LEFT, RIGHT, FIND, etc. I can get the player names from column G, so we only need a single formula in H you can drag down, and the same in I. But again, it would make them longer. Also, you can make the I formula into a "Last 10" matches formula just by changing the 5 in the J formula. Anyway, see if this does what you want.
 

jayyemm

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Thank you for the welcome Eric! I am typically a lurker however I found myself getting very frustrated with this so decided to sign up and give it a shot.

The helper column is not a problem, I am okay with hiding it but I probably should have mentioned that I am primarily using Google Sheets. I didn't think there was much difference for what I was using it for....until now. The AGGREGATE function is not recognized in Google Sheets. From what I see, this will work exactly how I would like it to. I tried doing some research on how to duplicate the AGGREGATE function in Google Sheets however, not having much luck.

Something else I would like to add is the overall last 5 results as well. Would this be done the same way? Player A's last 5 results overall may be much different compared to specifically versus Player B.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
You really should have mentioned that you are using Google Sheets. While some things are the same, some are vastly different. I could have tried a different approach, or someone with more knowledge of Google Sheets could have stepped in.

I rewrote the formulas without AGGREGATE. This requires array formulas. In Excel you specify an array formula by pressing Control+Shift+Enter, in Google Sheets you surround the formula with =ARRAYFORMULA(...). And that's about all I know about Google Sheets.

Book1
ABCDEFGHIJKLM
1ABCResultH2HH2HL5Name1Name2Row of 5th last match
2xxAA vs. B3-33-2AB5
3xxBA vs. C4-42-3AC12
4xxBB vs. C4-32-3BC9
5xxA
6xxB
7xxA
8xxBOverallABC
9xxC7-77-67-8
10xxC
11xxALast 5 row151518
12xxALast 5 record2-32-33-2
13xxB
14xxC
15xxA
16xxB
17xxC
18xxB
19xxC
20xxA
21xxC
22xxC
23
Sheet3
Cell Formulas
RangeFormula
H2:H4H2=SUMPRODUCT(MMULT((A$2:C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$25="x")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$25=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$25="x")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$25=L2))
I2:I4I2=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$25=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$25=L2))
K2:K4K2=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4L2=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4M2=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))="x")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))="x"),ROW(A$1:A$1000)),5)
G9:I9G9=COUNTIFS(INDEX($A:$C,0,MATCH(G8,$A$1:$C$1,0)),"x",$D:$D,G8)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G8,$A$1:$C$1,0)),"x",$D:$D,"<>"&G8)
G11:I11G11=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G8,$A$1:$C$1,0))="x"),ROW(A$1:A$1000)),5)
G12:I12G12=COUNTIFS(INDEX($A:$C,G11,MATCH(G8,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G8,$A$1:$C$1,0)),"x",INDEX($D:$D,G11):$D$1000,G8)&"-"&COUNTIFS(INDEX($A:$C,G11,MATCH(G8,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G8,$A$1:$C$1,0)),"x",INDEX($D:$D,G11):$D$1000,"<>"&G8)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


This has all the functions you asked for, using helper cells where appropriate. I hope it works for you, because I can't help any further. Good luck!
 

jayyemm

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

You are the man Eric! Works perfectly! Again, I am sorry for not mentioning that I am using Google Sheets. You should give yourself some more Google Sheets credit!

One last issue, I hope. What if the "x" was random text and not the same in each? They could be colors, shapes, rock, paper, scissors, etc while still not including blanks. Whatever replaces the "x" I want it to be able to still pull the same head to head data and last results. I tried replacing "x" in the formula's criteria with "<>" however that did not work.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
In the G12 COUNTIFS formula, you should be able to replace the "x" with "<>" as you suggested. In G9 formula, if you do the same thing, since I'm using whole column references, it'll count an extra loss for each player since it'll count the header row. Easiest way is to use "<>" and just put -1 on the end of the formula. Otherwise you'll need to change the references from whole column to $A$2:$A$1000 or whatever. For the other formulas, change ="x" to <>"" everywhere you see it.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,039
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I know you're on Google Sheets, but if you come over to Excel from the dark side then you can use cool things like Power Query to do this kind of thing.

Book1
ABCDEFGH
1Player APlayer BPlayer CResultMatchupH2HLast5
2xxAAB3-33-2
3xxBAC4-42-3
4xxBBC4-32-3
5xxA
6xxB
7xxA
8xxB
9xxC
10xxC
11xxA
12xxA
13xxB
14xxC
15xxA
16xxB
17xxC
18xxB
19xxC
20xxA
21xxC
22xxC
Sheet1


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Match = Table.AddColumn(Source, "Matchup", each if [Player A] = null then "BC" else if [Player B] = null then "AC" else "AB"),
    P1 = Table.AddColumn(Match, "P1", each if Text.Middle([Matchup],0,1) = [Result] then 1 else 0),
    P2 = Table.AddColumn(P1, "P2", each if [P1] = 1 then 0 else 1),
    Group = Table.Group(P2, {"Matchup"}, {{"P1", each List.Sum([P1]), type number}, {"P2", each List.Sum([P2]), type number}, {"Rows", each _, type table}}),
    Sort = Table.Sort(Group,{{"Matchup", Order.Ascending}}),
    H2H = Table.CombineColumns(Table.TransformColumnTypes(Sort, {{"P1", type text}, {"P2", type text}}, "en-US"),{"P1", "P2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"H2H"),
    Filter = Table.TransformColumns(H2H, {{"Rows", each Table.CombineColumns(Table.TransformColumnTypes(Table.Group(Table.LastN(Table.SelectRows(_, each [Matchup]=[Matchup]),5),{"Matchup"},{{"P1", each List.Sum([P1]), type number}, {"P2", each List.Sum([P2]), type number}, {"Rows", each _, type table}}),{{"P1", type text}, {"P2", type text}}, "en-US"),{"P1", "P2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Last5")}}),
    Last5 = Table.ExpandTableColumn(Filter, "Rows", {"Last5"}, {"Last5"})
in
    Last5
 

jayyemm

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Eric - I surprisingly was able to figure out the "-1" in the G9 formulas when I first saw your response. I am still having trouble with replacing "x" though. I've replaced all "x" with "<>" and get an error immediately almost everywhere except the overall record. I also tried rearranging the overall last 5 to vertical, to represent more of a 1st, 2nd 3rd standings look. Obviously I have run into errors there and not sure where to correct that. I downloaded the file on Excel and shared via XL2BB this time around. You can see in G7, G8 and G9 I've used a find 1st, 2nd, 3rd most common so that as records change/improve this will be done automatically.

lrobbo - That seems a lot more simple from the amount of text involved lol but I'm sure more complex than what it seems. I mainly use Google Sheets for the ease of sharing and working with others. I'm sure Microsoft has something similar but Google just makes it easier for everyone.

Matchups - Standings.xlsx
ABCDEFGHIJKLM
1ABCResultH2HL5Name 1Name 2Row of 5th Last Match
2xxAA vs. B0-0#NUM!AB#NUM!
3xxBA vs. C0-0#NUM!AC#NUM!
4xxBB vs. C0-0#NUM!BC#NUM!
5xxA
6xxBOverallL5Last 5 Row
7xxAB13-11#REF!#REF!
8xxBC13-13#REF!#REF!
9xxCA11-16#REF!#REF!
10xxC
11xxA
12xxA
13xxB
14xxC
15xxA
16xxB
17xxC
18xxB
19xxC
20xxA
21xxC
22xxC
23xxA
24xxB
25xxB
26xxA
27xxA
28xxC
29xxC
30xxB
31xxC
32xxB
33xxC
34xxB
35xxC
36xxB
37xxA
38xxC
Sheet4
Cell Formulas
RangeFormula
H2H2=SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$250="<<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=L2))
I2:I4I2=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=L2))
H3:H4H3=SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K3),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L3),{1;1;1}),--(D$2:D$250=K3))&"-"&SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K3),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L3),{1;1;1}),--(D$2:D$250=L3))
K2:K4K2=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4L2=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4M2=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))="<>")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))="<>"),ROW(A$1:A$1000)),5)
G7G7=INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))
H7:H9H7=COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,G7)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,"<>"&G7)
I7:I9I7=COUNTIFS(INDEX($A:$C,K7,MATCH(#REF!,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(#REF!,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,#REF!)&"-"&COUNTIFS(INDEX($A:$C,K7,MATCH(#REF!,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(#REF!,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,"<>"&#REF!)
G8G8=INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0))))
G9G9=INDEX(D1:D1001,MODE(IF(((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0)))))*(D1:D1001<>INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0)))))),MATCH(D1:D1001,D1:D1001,0))))
K7K7=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(#REF!,$A$1:$C$1,0))="<>"),ROW(A$1:A$1000)),5)
K8K8=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(#REF!,$A$1:$C$1,0))="<>"),ROW(B$1:B$1000)),5)
K9K9=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(#REF!,$A$1:$C$1,0))="<>"),ROW(C$1:C$1000)),5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

jayyemm

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
I was able to fix the rearranging, it was just a referencing thing and everything worked but as soon as I replace all the "x" with "<>" I get the errors.

Matchup - Standings.xlsx
ABCDEFGHIJKLM
1ABCResultH2HL5Name1Name2Row of 5th last match
2xxAA vs. B0-0#NUM!AB#NUM!
3xxBA vs. C0-0#NUM!AC#NUM!
4xxBB vs. C0-0#NUM!BC#NUM!
5xxA
6xxBOverallL5Last 5 Row
7xxAB13-11#NUM!#NUM!
8xxBC13-13#NUM!#NUM!
9xxCA11-16#NUM!#NUM!
10xxC
11xxA
12xxA
13xxB
14xxC
15xxA
16xxB
17xxC
18xxB
19xxC
20xxA
21xxC
22xxC
23xxA
24xxB
25xxB
26xxA
27xxA
28xxC
29xxC
30xxB
31xxC
32xxB
33xxC
34xxB
35xxC
36xxB
37xxA
38xxC
Sheet4
Cell Formulas
RangeFormula
H2:H4H2=SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=L2))
I2:I4I2=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=L2))
K2:K4K2=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4L2=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4M2=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))="<>")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))="<>"),ROW(A$1:A$1000)),5)
G7G7=INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))
H7:H9H7=COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,G7)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,"<>"&G7)
I7:I9I7=COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,G7)&"-"&COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,"<>"&G7)
G8G8=INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0))))
G9G9=INDEX(D1:D1001,MODE(IF(((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0)))))*(D1:D1001<>INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0)))))),MATCH(D1:D1001,D1:D1001,0))))
K7K7=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G7,$A$1:$C$1,0))="<>"),ROW(A$1:A$1000)),5)
K8K8=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G8,$A$1:$C$1,0))="<>"),ROW(B$1:B$1000)),5)
K9K9=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G9,$A$1:$C$1,0))="<>"),ROW(C$1:C$1000)),5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
I'm not sure where you got your G7:G9 formulas, so I just put the values from your sheet into those cells. Then for the rest of the formulas, I showed how to replace the ="x" with <>"".

Book1
ABCDEFGHIJKLM
1ABCResultH2HL5Name1Name2Row of 5th last match
2xxAA vs. B6-63-2AB26
3xxBA vs. C5-90-5AC28
4xxBB vs. C7-43-2BC22
5xxA
6xxBOverallL5Last 5 Row
7xxAB13-114-130
8xxBC13-134-131
9xxCA11-161-433
10xxC
11xxA
12xxA
13xxB
14xxC
15xxA
16xxB
17xxC
18xxB
19xxC
20xxA
21xxC
22xxC
23xxA
24xxB
25xxB
26xxA
27xxA
28xxC
29xxC
30xxB
31xxC
32xxB
33xxC
34xxB
35xxC
36xxB
37xxA
38xxC
39
Sheet8
Cell Formulas
RangeFormula
H2:H4H2=SUMPRODUCT(MMULT((A$2:C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=L2))
I2:I4I2=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=L2))
K2:K4K2=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4L2=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4M2=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))<>"")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))<>""),ROW(A$1:A$1000)),5)
H7:H9H7=COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,G7)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,"<>"&G7)
I7:I9I7=COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,G7)&"-"&COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,"<>"&G7)
K7:K9K7=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G7,$A$1:$C$1,0))<>""),ROW(A$1:A$1000)),5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,388
Messages
5,601,382
Members
414,448
Latest member
Jessica 22664

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top