SUMIFS based on mulitiple criteria of non-blank cells to determine goals scored and allowed...

[mad3]

I am trying to determine which players are on the field together when goals are allowed. I have 20 sheets with same exact format for a scoresheet as below. The goal is to identify three specific players that are on field at same time and sum the results for my team; to sum goals allowed for each team while the three specific players are playing. I want to be able to easily adjust formally to check various combinations.

CM all-stars 2024 STATS.xlsm
	A	B	C	D	E	F	G	H	I
5	Team		x	1	2	3	4	OT	T
6	West Falls			1	0	1	1		3
7	South Fork			0	2	0	0		2
8				x	x		x
9	gm	Player	GP	1	2	3	4	QP	GS
10	1	Aaron	1		ST	ST	LM	3	1
11	1	Anthony	1	LM		LM		2
12	1	August	1	LD	RM		LD	3
13	1	Cole	1		RD	RD		2
14	1	Clayton	1	ST		RM	ST	3	1
15	1	Cruz	1	CM		LD	CM	3
16	1	Diego		GK	GK	GK		3
17	1	Dylan	1	RM	LD		RM	3	2
18	1	Jake	1		CD	CD	RD	3
19	1	Masso	1	RD	LM		GK	3
20
21			1	7	7	7	7	28	4
GM9

Cell Formulas
Range	Formula
I6:I7	I6	=SUM(D6:G6)
H10:H19	H10	=COUNTA(Table2202224262579[@[1]:[4]])
D21:G21	D21	=COUNTA(D10:D19)
H21:I21	H21	=SUM(H10:H20)

 

[Kaper]

Probably the question is unanswered that long, because for those who are better in excell than in understanding of "your sport" it is not clear:
- what does mean: "when goals are allowed"
- What is the meaning of bold font on some LD CD and RD texts in the sheet.
- How would you manually do your first goal for that particular sample data: "identify three specific players..."

 

[mad3]

Probably the question is unanswered that long, because for those who are better in excell than in understanding of "your sport" it is not clear:
- what does mean: "when goals are allowed"
- What is the meaning of bold font on some LD CD and RD texts in the sheet.
- How would you manually do your first goal for that particular sample data: "identify three specific players..."

Hi,

Thanks for response. The bold is for me. I changed formation in second quarter, so I made it bold so I wouldn't forget when reviewing at later date.

The goal of what I'm trying to do, Is to be able to sum goals allowed while specific players are on the field. I have about 20 sheets within same workbook just like this one. Each has a line score by quarter, followed by the line-up below it. The line-up shows positions played by quarter long with several columns of other statistics.

Currently, I have each sheet sum to a totals sheet. I have a sheet in front called first and one at the end called last. All the formulas for summing start First:Last! followed by cell being summed. This allows me to quickly move the "first" and "last" sheet if I want totals by a specific group if games. Such as League, tournament Or playoffs.

Hopefully this gives an idea of what I'm doing.

Thanks,

Mike

 

[Kaper]

Hi Mike,
As a matter of fact - for me only bold font is really clear.

I could expect that as you have 4 quarters the discipline could be american football (I know, I shall say just football, the european footbal is a mere socccer - anyway there shall be more players for football), basketball or ... water-polo.

So saying identify 3 is to test all possible triples
like based on 1st Q:
Anthony August Clayton
Anthony August Cruz
Anthony August Diego
...
Anthony August Masso
Anthony Clayton Cruz
Anthony Clayton Diego
...
Anthony Clayton MAsso
Anthony Cruz Diego
and so on?

And then for every triplet assign goals from that quarter? then move to next quarter and repeat that operation?

But I suspect the thing you are trying to find is a typical for that discipline and it's just me not knowing that, as I am interested in other sports. Like ski-jumping and so on.

 

[mad3]

Hi Mike,
As a matter of fact - for me only bold font is really clear.

I could expect that as you have 4 quarters the discipline could be american football (I know, I shall say just football, the european footbal is a mere socccer - anyway there shall be more players for football), basketball or ... water-polo.

So saying identify 3 is to test all possible triples
like based on 1st Q:
Anthony August Clayton
Anthony August Cruz
Anthony August Diego
...
Anthony August Masso
Anthony Clayton Cruz
Anthony Clayton Diego
...
Anthony Clayton MAsso
Anthony Cruz Diego
and so on?

And then for every triplet assign goals from that quarter? then move to next quarter and repeat that operation?

But I suspect the thing you are trying to find is a typical for that discipline and it's just me not knowing that, as I am interested in other sports. Like ski-jumping and so on.

You are on the right track. I have worked this by hand and found a combination where the worst case scenarios seem to happen. Cole, Clayton and Aaron.