Cricket Points Table

[amruthubballi]

Hi,

I am working on cricket points table, My table goes like this.

Match Schedule

1	Home Team(A1)	Away Team(B1)	Result(C1)
2	RPS	KKR
3	RCB	RCB
4	KKR	KKR

<tbody>
</tbody>

Points Table

1	Teams(E1)	Games Played(F1)	Wins(G1)	Points(H1)
2	RPS
3	KKR
4	RCB
5	GL

<tbody>
</tbody>

I am making predections and working permutations so that my favourite team can go for playoffs. I need a formula, when I enter the winner in result column(C1) in Match schedule, Points table needs to be updated for those who played the game.

E.g If I enter the winner of 1st match as KKR in Result(C2), team played(RPS & KKR) should be shown indicating as 1 match played in points table i.e 1 in Games Played(F2) and 1 in Games Played(F3), smilarly if I enter the result of 2nd match as RCB then teams played(RCB & RPS) should shown as 1 in Games Played(F4) and 2 in Games Played(F2) (as RPS have played 2nd match in season)

In Games Played(F) column I want to indicate the number of matches played only after I enter results. Hope I have written enough to understand my problem and to resolve, Please someone help me.

Thank You

 

[FormR]

Hi, welcome to the forum!

Something like this maybe:

Excel 2013
	A	B	C	D	E	F	G	H
1	Home Team(A1)	Away Team(B1)	Result(C1)		Teams(E1)	Games Played(F1)	Wins(G1)	Points(H1)
2	RPS	KKR	KKR		RPS	2	0
3	RCB	RPS	RCB		KKR	2	2
4	KKR	AAA	KKR		RCB	1	1
5					GL	0	0
Sheet1

Cell Formulas
Range	Formula
F2	=COUNTIFS(A:A,E2,C:C,"<>")+COUNTIFS(B:B,E2,C:C,"<>")
G2	=COUNTIFS(C:C,E2)

 

[steve the fish]

You dont seem to have made any provision for if a game is called off and both teams share the 2 points so you could maybe use something like this in G2:

=COUNTIF($C:$C,E2)+COUNTIFS($A:$A,E2,$C:$C,"rain")/2+COUNTIFS($B:$B,E2,$C:$C,"rain")/2

instead of typing the winner in column C you type 'rain' if a game is called off.

 

[amruthubballi]

Thank you so much... it really helped me a lot.

 

[amruthubballi]

Thank you so much... Now I have created as No Result column.