Two-part Question about Z (summa) and calculations

[kmprice710]

I've been asked to updated my alma mater's girls' basketball team files and I found some problems.

Is there a way to do the following two things without using VBA?

1. All of the games' boxscores are listed in one giant file, one under the other. They all have a game ID code.
The problem is that the Totals rows don't have MIN totaled up. This should be the sum of the minutes for each team's players. For example cell E17 should say 200.
Is there a formula that I can write that will fill in just that column in the Totals row for each game?

2. Then I want to fill in the Team row by taking the Totals row and finding the Difference between it and the sum of each team's individual players for every category. For example, cell M16 should be 1 because the Totals row for Oreb is 17 but the sum of the players comes out to be only 16. When the sum is larger than Team, then the difference should be negative.
Is there a formula that would work for that as well?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	season	game	school	player	MIN	fgm	fga	tgm	tga	ftm	fta	pts	oreb	dreb	reb	ast	to	stl	blk	pf
2	2002-03	0128200306370177	Central	Young	23	3	7	0	2	0	0	6	1	4	5	2	0	2	0	1
3	2002-03	0128200306370177	Central	Davis	34	8	17	0	0	0	0	16	4	7	11	0	1	0	2	3
4	2002-03	0128200306370177	Central	Anderson	25	3	9	0	1	0	0	6	4	3	7	1	2	1	0	4
5	2002-03	0128200306370177	Central	Russell	24	2	11	0	0	0	0	4	0	2	2	0	2	0	1	2
6	2002-03	0128200306370177	Central	Smith	26	1	7	0	4	3	3	5	1	0	1	5	4	1	0	1
7	2002-03	0128200306370177	Central	Faulkner	11	1	2	1	1	0	0	3	1	1	2	0	1	0	0	0
8	2002-03	0128200306370177	Central	Ragland	10	0	3	0	0	0	0	0	1	0	1	0	0	0	0	2
9	2002-03	0128200306370177	Central	Wilson	3	1	3	1	3	0	0	3	1	1	2	1	1	0	0	1
10	2002-03	0128200306370177	Central	Hayes	3	0	2	0	1	0	0	0	0	0	0	0	1	0	0	0
11	2002-03	0128200306370177	Central	Wilson	3	1	1	1	1	0	0	3	0	1	1	0	0	0	0	0
12	2002-03	0128200306370177	Central	Eason	7	1	2	0	0	0	0	2	0	0	0	0	1	0	0	2
13	2002-03	0128200306370177	Central	Armstead	12	0	3	0	1	0	0	0	1	3	4	0	2	0	0	1
14	2002-03	0128200306370177	Central	Benson	3	0	1	0	0	0	0	0	1	0	1	0	0	0	0	0
15	2002-03	0128200306370177	Central	Brown	16	1	6	0	0	1	1	3	1	3	4	1	1	0	0	2
16	2002-03	0128200306370177	Central	TEAM
17	2002-03	0128200306370177	Central	Totals		22	74	3	14	4	4	51	17	26	43	10	16	4	3	19
18	2002-03	0128200306370177	Lincoln	Jacobs	22	0	4	0	2	0	0	0	1	5	6	3	0	0	1	0
19	2002-03	0128200306370177	Lincoln	Hart	21	9	10	0	0	1	3	19	3	7	10	3	0	1	1	0
20	2002-03	0128200306370177	Lincoln	Rollison	20	4	8	2	4	2	2	12	0	3	3	1	2	1	0	2
21	2002-03	0128200306370177	Lincoln	Schroeder	27	3	5	2	4	2	2	10	0	4	4	6	2	1	1	1
22	2002-03	0128200306370177	Lincoln	Harms	26	6	11	5	8	0	0	17	1	4	5	1	0	0	0	0
23	2002-03	0128200306370177	Lincoln	Cameron	14	1	2	0	0	0	0	2	1	2	3	0	2	1	0	1
24	2002-03	0128200306370177	Lincoln	James	27	0	3	0	1	5	6	5	1	3	4	4	3	1	0	2
25	2002-03	0128200306370177	Lincoln	Mays	5	0	2	0	1	0	0	0	0	0	0	1	0	0	0	0
26	2002-03	0128200306370177	Lincoln	Hadley	12	2	4	0	0	2	6	6	0	2	2	0	2	0	2	0
27	2002-03	0128200306370177	Lincoln	Broomfield	11	1	3	1	2	1	2	4	0	0	0	1	2	1	0	0
28	2002-03	0128200306370177	Lincoln	Wilkins	4	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0
29	2002-03	0128200306370177	Lincoln	Jenkins	11	2	4	0	0	3	4	7	1	2	3	0	0	0	0	1
30	2002-03	0128200306370177	Lincoln	TEAM
31	2002-03	0128200306370177	Lincoln	Totals		28	57	10	23	16	25	82	8	35	43	20	13	6	5	7
32	2008-09	1118200808220383	Central	Young	30	7	12	0	0	5	8	19	2	3	5	2	0	0	0	5
33	2008-09	1118200808220383	Central	Davis	30	5	11	2	8	3	4	15	0	3	3	0	0	1	0	3
34	2008-09	1118200808220383	Central	Anderson	28	5	11	2	6	1	1	13	1	2	3	3	5	2	0	3
35	2008-09	1118200808220383	Central	Russell	30	1	2	0	0	0	0	2	0	1	1	1	0	1	0	5
36	2008-09	1118200808220383	Central	Smith	12	0	2	0	0	2	2	2	1	1	2	0	1	0	0	1
37	2008-09	1118200808220383	Central	Faulkner	23	5	7	4	6	3	4	17	0	2	2	0	1	1	0	3
38	2008-09	1118200808220383	Central	Ragland	13	1	2	0	1	5	6	7	0	1	1	3	1	0	0	1
39	2008-09	1118200808220383	Central	Wilson	19	1	6	1	3	2	2	5	0	2	2	0	1	2	0	2
40	2008-09	1118200808220383	Central	Hayes	9	0	0	0	0	0	0	0	1	1	2	0	0	0	0	0
41	2008-09	1118200808220383	Central	Wilson	6	0	1	0	1	0	0	0	0	0	0	0	1	0	0	0
42	2008-09	1118200808220383	Central	TEAM									1	0	1
43	2008-09	1118200808220383	Central	Totals		25	54	9	25	21	27	80	6	16	22	9	10	7	0	23
44	2008-09	1118200808220383	Grant	Tolkien	37	13	17	4	5	11	13	41	0	1	1	4	2	3	0	1
45	2008-09	1118200808220383	Grant	Solomon	22	3	6	0	0	4	4	10	0	1	1	0	0	1	0	2
46	2008-09	1118200808220383	Grant	Brent	22	3	5	0	0	2	2	8	7	3	10	1	0	0	0	0
47	2008-09	1118200808220383	Grant	Jordan	32	3	7	0	2	1	2	7	1	4	5	4	1	1	1	3
48	2008-09	1118200808220383	Grant	Olsen	33	1	6	1	3	2	2	5	0	7	7	0	4	0	0	4
49	2008-09	1118200808220383	Grant	Baker	18	3	3	0	0	2	2	8	1	4	5	0	1	1	1	1
50	2008-09	1118200808220383	Grant	Taylor	19	2	6	0	2	2	2	6	1	2	3	3	1	0	0	1
51	2008-09	1118200808220383	Grant	Burnley	11	1	4	1	4	0	0	3	0	2	2	1	1	0	0	2
52	2008-09	1118200808220383	Grant	Curtis	4	1	1	0	0	0	0	2	0	0	0	0	1	0	0	2
53	2008-09	1118200808220383	Grant	Scobey	2	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
54	2008-09	1118200808220383	Grant	TEAM									0	0	0
55	2008-09	1118200808220383	Grant	Totals		30	55	6	16	24	27	90	10	24	34	13	12	6	2	16

 

[Fluff]

Is there a formula that I can write that will fill in just that column in the Totals row for each game?

No, you would need to add the formula to E17,E31 etc individually.

 

[kmprice710]

Oh gosh there are 10,000 rows here, like 20 games a year for 20 years. I can probably filter by Totals to get each Totals row and paste the formula.

I can't just use Z (summa) though because the number of rows for each game changes based on the number of players who appeared.

Is there a way to have that be variable?

 

[Fluff]

What exactly do you mean by Z (summa)?

 

[RoryA]

Filter the data for Totals. Select the visible cells in the MIN column using Alt+; and then remove the filter. Press Alt+= and that should enter the required SUM formula in each cell - though it will include the TEAM row. Not sure if that's an issue.

 

[kmprice710]

What exactly do you mean by Z (summa)?

Sigma, auto sum

 

[kmprice710]

Filter the data for Totals. Select the visible cells in the MIN column using Alt+; and then remove the filter. Press Alt+= and that should enter the required SUM formula in each cell - though it will include the TEAM row. Not sure if that's an issue.

ok I filtered by Totals but it returned 0 for every subtotal

 

[RoryA]

If you did exactly what I said, you would have SUM formulas not SUBTOTAL? If they return 0 then it would suggest the numbers are actually text.

 

[kmprice710]

Nope they're numbers. Maybe I did not understand your alt command?

 

[RoryA]

Ok, so the steps are:
1. Filter for Totals
2. Select the (blank) Min cells
3. Press Alt and semicolon together to select only the visible cells.
4. Remove the filter (without changing the selected cells)
5. Press Alt and the = key, which will enter the required SUM formula in each of the selected cells.