Reset calculation when year changes

[tigerzen]

The tally formula needs to reset when the year changes, the entries in yellow are therefore incorrect, the column on the right contains the correct values. There may be a better way of calculating the tally, if not I'll stick with the COUNTIF function. We require a solution for Excel 2019 please.

Football stats functions.xlsx
	A	B	C	D	E	F
1	Game	Result	Year	Win Draw Loss		Should be
2	Game 1	D	2022	0-1-0
3	Game 2	W	2022	1-1-0
4	Game 3	L	2022	1-1-1
5	Game 4	L	2022	1-1-2
6	Game 5	L	2022	1-1-3
7	Game 6	L	2022	1-1-4
8	Game 7	L	2022	1-1-5
9	Game 8	W	2022	2-1-5
10	Game 1	W	2023	3-1-5		1-0-0
11	Game 2	L	2023	3-1-6		1-0-1
12	Game 3	L	2023	3-1-7		1-0-2
13	Game 1	W	2024	4-1-7		1-0-0
14	Game 2	W	2024	5-1-7		2-0-0
15	Game 3	W	2024	6-1-7		3-0-0
Sheet14

Cell Formulas
Range	Formula
D2:D15	D2	=COUNTIF($B$2:B2,"W")&"-"&COUNTIF($B$2:B2,"D")&"-"&COUNTIF($B$2:B2,"L")

 

[Phuoc]

Use COUNTIFS:

Book1
	A	B	C	D	E	F
1	Game	Result	Year	Win Draw Loss		Should be
2	Game 1	D	2022	0-1-0	0-1-0
3	Game 2	W	2022	1-1-0	1-1-0
4	Game 3	L	2022	1-1-1	1-1-1
5	Game 4	L	2022	1-1-2	1-1-2
6	Game 5	L	2022	1-1-3	1-1-3
7	Game 6	L	2022	1-1-4	1-1-4
8	Game 7	L	2022	1-1-5	1-1-5
9	Game 8	W	2022	2-1-5	2-1-5
10	Game 1	W	2023	3-1-5	1-0-0	1-0-0
11	Game 2	L	2023	3-1-6	1-0-1	1-0-1
12	Game 3	L	2023	3-1-7	1-0-2	1-0-2
13	Game 1	W	2024	4-1-7	1-0-0	1-0-0
14	Game 2	W	2024	5-1-7	2-0-0	2-0-0
15	Game 3	W	2024	6-1-7	3-0-0	3-0-0
Sheet1

Cell Formulas
Range	Formula
D2:D15	D2	=COUNTIF($B$2:B2,"W")&"-"&COUNTIF($B$2:B2,"D")&"-"&COUNTIF($B$2:B2,"L")
E2:E15	E2	=COUNTIFS($C$2:C2,C2,$B$2:B2,"W")&"-"&COUNTIFS($C$2:C2,C2,$B$2:B2,"D")&"-"&COUNTIFS($C$2:C2,C2,$B$2:B2,"L")

 

[tigerzen]

Thanks Phuoc, that is perfect. I'd been mucking around with IF functions trying to make this work when all I had to do was add some more criteria to the COUNTIF function.