Calculating W/L - multiple tracks

[Hutchcove]

Hi, all,

Trying to find a way to explain what I need so bear with me.

My daughter and I have setup 2 marble tracks. We have 5 sets of colors marbles and we want to have mini tournaments (seasons) racing the 5 different colors over 2 tracks.

It is very easy to calculate the W/L as a total using the SUMIF command but I would like some additional statistics (as I enjoy stats in a small way).

If I have 4 columns (A1=track1, B1=Team, C1=W, D1=L (Either a W or L)
vs (A2=track2, B2=Team, C2=W, D2=L (Either a W or L)

I run the entire season, down the rows, with that method. Unsure on how to pick up the Wins for any given team on Track 1 & then on Track 2. After which I would just total them to have the overall standings. I guess the same goes for Losses on each track for each team.

If you need more, please let me know and I will see what I can do to clarify. Thank you all.

Hutchcove & daughter

 

[palaeontology]

Can you provide an example of what the sheet might look like for a season (using made up data).

 

[FDibbins]

I think you need to look at the COUNTIFS() function
=COUNTIFS(team-range, team1, track range, track1)
etc
You could reference the team and track, instead of hard coding them

 

[Hutchcove]

Team	W	L	%			Track #	Team	W	L
Green	6	2	0.750			1	Green	1
Orange	4	4	0.500			2	Yellow		1
Red	4	4	0.500			1	Orange	1
Yellow	3	5	0.375			2	Purple		1
Purple	3	5	0.375			1	Red		1
						2	Green	1
Track 1						1	Yellow	1
Team	W	L	%			2	Orange		1
Orange						1	Purple		1
Green						2	Red	1
Red						1	Green	1
Yellow						2	Purple		1
Purple						1	Yellow		1
						2	Red	1
Track 2						1	Orange		1
Team	W	L	%			2	Green	1
Orange						1	Red		1
Green						2	Orange	1
Red						1	Purple	1
Yellow						2	Yellow		1
Purple						1	Yellow	1
						2	Green		1
						1	Purple	1
						2	Orange		1
						1	Green	1
						2	Red		1
						1	Orange		1
						2	Yellow	1
						1	Red	1
						2	Purple		1
						1	Purple		1
						2	Green	1
						1	Red	1
						2	Yellow		1
						1	Green		1
						2	Orange	1
						1	Orange	1
						2	Red		1
						1	Yellow		1
						2	Purple	1

<colgroup><col width="64" span="6" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Hutchcove]

I do not know how to add a file. Any suggestions to show you my format?

 

[FDibbins]

You cannot upload files here

See if you can adapt this...

	H​	I​	J​	K​	L​
1​	name	Track	Name	Track	Count
2​	aa	1​	aa	1​	2​
3​	bb	2​
4​	cc	3​
5​	aa	4​
6​	bb	1​
7​	cc	2​
8​	aa	3​
9​	bb	4​
10​	cc	1​
11​	aa	1​

J and K would be the criteria
L2=COUNTIFS($H$2:$H$11,J2,$I$2:$I$11,K2)

 

[Hutchcove]

Track name	Team	W	L	DNF
Track A	Green	1
Track B	Red		1
Track A	Orange		1
Track B	Yellow	1

<tbody>
</tbody>

I would like for my standings to shows W/L for each team for any given Track.

That way I take the total from Track A & Track B to have the full standings (regardless of track)

 

[FDibbins]

Instead of a W and a L column, maybe have just 1 and record a W or L?

 

[Hutchcove]

Track name	Team	W/L		DNF
Track A	Green	W
Track B	Red	L
Track A	Orange	L
Track B	Yellow	W

<tbody>
</tbody>

What formula would I need to benifit from this change?

Thanks

 

[FDibbins]

Maybe this?

	N​	O​	P​	Q​	R​	S​
1​	Track name	Team	W/L	DNF	Track	Track A
2​	Track A	Green	W		Team	Green
3​	Track B	Red	L		W/L	W
4​	Track A	Orange	L		Count	1​
5​	Track B	Yellow	W

S4=COUNTIFS($N$2:$N$5,S1,$O$2:$O$5,S2,$P$2:$P$5,S3)