Counting same value across a spreadsheet

[Albertie]

Hi,
Im trying to count performance stats for a team (points scored, player of match etc.) across a spreadsheet that contains the teams picked for each game of the season. see picture attached for basic example. Is there anyway to sum an individual players performance (say Mary & points scored) across all the games, bearing in mind that they may not play in same positions every game (so wont be in same position in column for each game.)

Cheers.
A

 

[felixstraube]

Assuming you are adding new games to the columns to the right... how about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Youghal (A) Friendly					Youghal (H) O'Neill Cup					Another (A) Game
2	Team	321	Try scored	Points kicked	Injury	Team	321	Try scored	Points kicked	Injury	Team	321	Try scored	Points kicked	Injury
3	John	3	5	2	Concussion	Mary	1	5			Mary	1
4	Joe					Sean	2		2	Ribs	Sean	3	5
5	Mary	2				Joe	3	10			John	2		2
6	Sean	1	5			John					Joe		10
7
8
9	Scores of all games
10		321	Try scored	Points kicked
11	John	5	5	4
12	Mary	4	5	0
13	Joe	3	20	0
14	Sean	6	10	2
Sheet1

Cell Formulas
Range	Formula
B11:D14	B11	=LET(d, FILTER($A$3:$ALL$6,$A$2:$ALL$2<>""), sd, DROP(WRAPROWS(TOROW(d), 5),,-1), fd, DROP(FILTER(sd, CHOOSECOLS(sd,1)=A11),,1), res, BYCOL(fd, LAMBDA(x, SUM(x))), res )
Dynamic array formulas.

 

[Albertie]

Assuming you are adding new games to the columns to the right... how about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Youghal (A) Friendly					Youghal (H) O'Neill Cup					Another (A) Game
2	Team	321	Try scored	Points kicked	Injury	Team	321	Try scored	Points kicked	Injury	Team	321	Try scored	Points kicked	Injury
3	John	3	5	2	Concussion	Mary	1	5			Mary	1
4	Joe					Sean	2		2	Ribs	Sean	3	5
5	Mary	2				Joe	3	10			John	2		2
6	Sean	1	5			John					Joe		10
7
8
9	Scores of all games
10		321	Try scored	Points kicked
11	John	5	5	4
12	Mary	4	5	0
13	Joe	3	20	0
14	Sean	6	10	2
Sheet1

Cell Formulas
Range	Formula
B11:D14	B11	=LET(d, FILTER($A$3:$ALL$6,$A$2:$ALL$2<>""), sd, DROP(WRAPROWS(TOROW(d), 5),,-1), fd, DROP(FILTER(sd, CHOOSECOLS(sd,1)=A11),,1), res, BYCOL(fd, LAMBDA(x, SUM(x))), res )
Dynamic array formulas.

Thanks for that, but I'm getting a #CALC! error when I copied your formula - =LET(d, FILTER($A$3:$ALL$6,$A$2:$ALL$2<>""),sd, DROP(WRAPROWS(TOROW(d), 5),,-1),fd, DROP(FILTER(sd, CHOOSECOLS(sd,1)=A11),,1),res, BYCOL(fd, LAMBDA(x, SUM(x))),res) into my spreadsheet

 

[Albertie]

Thanks for that, but I'm getting a #CALC! error when I copied your formula - =LET(d, FILTER($A$3:$ALL$6,$A$2:$ALL$2<>""),sd, DROP(WRAPROWS(TOROW(d), 5),,-1),fd, DROP(FILTER(sd, CHOOSECOLS(sd,1)=A11),,1),res, BYCOL(fd, LAMBDA(x, SUM(x))),res) into my spreadsheet

Assuming you are adding new games to the columns to the right... how about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Youghal (A) Friendly					Youghal (H) O'Neill Cup					Another (A) Game
2	Team	321	Try scored	Points kicked	Injury	Team	321	Try scored	Points kicked	Injury	Team	321	Try scored	Points kicked	Injury
3	John	3	5	2	Concussion	Mary	1	5			Mary	1
4	Joe					Sean	2		2	Ribs	Sean	3	5
5	Mary	2				Joe	3	10			John	2		2
6	Sean	1	5			John					Joe		10
7
8
9	Scores of all games
10		321	Try scored	Points kicked
11	John	5	5	4
12	Mary	4	5	0
13	Joe	3	20	0
14	Sean	6	10	2
Sheet1

Cell Formulas
Range	Formula
B11:D14	B11	=LET(d, FILTER($A$3:$ALL$6,$A$2:$ALL$2<>""), sd, DROP(WRAPROWS(TOROW(d), 5),,-1), fd, DROP(FILTER(sd, CHOOSECOLS(sd,1)=A11),,1), res, BYCOL(fd, LAMBDA(x, SUM(x))), res )
Dynamic array formulas.

Apologies, felixstraube, I moved a row number and got it working. Thank you very much. Much appreciated.

 

[felixstraube]

Thanks for the feedback. Happy to help.