Sum with multiple criteria

[D-Angle]

I am trying to get my soccer stats spreadsheet to do some complicated adding up for me and not getting anywhere fast. I have the following spreadsheet with the season's game results so far, set up so I can copy and paste them straight from England - Premier League to update the totals on the right and bottom. A separate tab has the current league table, which updates the number of games played on the left, and the league position on the left and across the top.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Games played	Lge Pos.		4	13	9	2	16	7	18	14	1	3	6	15	10	12	20	19	5	8	11	17	Goals scored at home	Goals conceded at home
2				Arsenal	Bournemouth	Burnley	Chelsea	Crystal Palace	Everton	Hull City	Leicester City	Liverpool	Manchester City	Manchester Utd	Middlesbrough	Southampton	Stoke City	Sunderland	Swansea City	Tottenham	Watford	West Bromwich	West Ham Utd
3	11	4	Arsenal				3-0					3-4			0-0	2-1			3-2	1-1				12	8
4	11	13	Bournemouth						1-0	6-1				1-3				1-2		0-0		1-0		10	6
5	11	9	Burnley	0-1				3-2	2-1	1-1		2-0							0-1		2-0			10	6
6	11	2	Chelsea			3-0			5-0		3-0	1-2		4-0									2-1	18	3
7	11	16	Crystal Palace		1-1							2-4					4-1					0-1	0-1	7	8
8	11	7	Everton					1-1							3-1		1-0			1-1			2-0	8	3
9	11	18	Hull City	1-4			0-2				2-1			0-1		2-1	0-2							5	11
10	11	14	Leicester City	0-0		3-0		3-1								0-0			2-1			1-2		9	4
11	11	1	Liverpool							5-1	4-1			0-0							6-1	2-1		17	4
12	11	3	Manchester City		4-0				1-1						1-1	1-1		2-1					3-1	12	5
13	11	6	Manchester Utd			0-0					4-1		1-2			2-0	1-1							8	4
14	11	15	Middlesbrough		2-0			1-2									1-1			1-2	0-1			5	6
15	11	10	Southampton			3-1	0-2											1-1	1-0		1-1			6	5
16	11	12	Stoke City										1-4					2-0	3-1	0-4		1-1		7	10
17	11	20	Sunderland	1-4				2-3	0-3						1-2							1-1		5	13
18	11	19	Swansea City				2-2			0-2		1-2	1-3	1-3							0-0			5	12
19	11	5	Tottenham					1-0			1-1	1-1	2-0					1-0						6	2
20	11	8	Watford	1-3	2-2		1-2			1-0				3-1										8	8
21	11	11	West Bromwich						1-2				0-4		0-0					1-1			4-2	6	9
22	11	17	West Ham Utd		1-0										1-1	0-3	1-1	1-0			2-4			6	9
23				Goals Scored Away
24				12	3	1	8	9	7	5	4	13	13	8	5	6	6	4	5	9	7	6	5
25				Goals Conceded Away
26				3	10	9	6	11	10	13	14	10	5	9	6	7	8	8	9	4	11	6	11

<tbody>
</tbody>

Sheet1​

I have used formulas to sum the first or last digit in a group of cells to get the total goals scored/conceded at home and away, for example Arsenal's total goals scored at home is calculated by {=SUM(IF(ISNUMBER(LEFT(D3:W3,1)+0),LEFT(D3:W3,1)+0))}. The next thing I want to work out is goals scored/conceded using the teams' league positions as criteria, but I can't think how to narrow down the criteria in this way. Here's what I have tried to get it to work out with no success:

• Total number of games played against a team currently in a higher league position (home and away)
• Total number of games played against a team currently in a lower league position (home and away)
• Total number of goals scored against a team currently in a higher league position (home and away)
• Total number of goals scored against a team currently in a lower league position (home and away)
• Total number of goals conceded against a team currently in a higher league position (home and away)
• Total number of goals conceded against a team currently in a lower league position (home and away

Any ideas of where to start greatly appreciated.

 

[D-Angle]

I have been thinking on this overnight and think I have the logic of the formula, I just can't get the formula itself.

So, for example if working out the number of goals Arsenal have scored at home against a team in a higher league position; you would need to count the leftmost digit in all cells in the range D3:W3, as I have done in X3, but with the added criteria of only counting cells in the columns where the number in the range D1:W1 was less than the number in B3.

Does that make sense? Brain hurting now.