LOOKUP / SUBTOTAL formula based on set criterior

[Jzfbkr]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Formation[/TD]
[TD]Scored[/TD]
[TD]Conceeded[/TD]
[TD]Home / Away[/TD]
[TD]Result[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]4-4-2
[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]H[/TD]
[TD]W
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3-5-2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]H[/TD]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4-3-3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]L[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4-4-2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]










[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Formation[/TD]
[TD]Total Conceeded[/TD]
[TD]Total Scored[/TD]
[TD]#W[/TD]
[TD]#D[/TD]
[TD]#L[/TD]
[TD]Total Points[/TD]
[/TR]
[TR]
[TD]4-4-2[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


Based on the table above does anybody know formulas that can return the following information. I have been trying to use a LOOKUP function but having no joy.

Any help on this would be massively appreciated.

Thanks

 

[Momentman]

Won't a SUMIF and COUNTIF work?

Excel 2010
	A	B	C	D	E	F	G
1	Formation	Scored	Conceeded	Home / Away	Result	Points
2	4-4-2	4	2	H	W	3
3	3-5-3	2	2	H	D	1
4	4-3-3	0	1	A	L	0
5	4-4-2	1	1	A	D	1
6
7	Formation	Total Conceeded	Total Scored	#W	#D	#L	Total Points
8	4-4-2	3	5	1	1	0	4
Sheet3

Cell Formulas
Range	Formula
B8	=SUMIF($A$2:$A$5,$A$8,$C$2:$C$5)
C8	=SUMIF($A$2:$A$5,$A$8,$B$2:$B$5)
D8	=COUNTIFS($A$2:$A$5,$A$8,$E$2:$E$5,"W")
E8	=COUNTIFS($A$2:$A$5,$A$8,$E$2:$E$5,"D")
F8	=COUNTIFS($A$2:$A$5,$A$8,$E$2:$E$5,"L")
G8	=D8*3+E8

 

[Jzfbkr]

Brilliant!! Sorry I don't know why I didn't think of that in the first place! Thanks for your help.

If there a way to apply these formulas to only visible cells? For instance when I filter data I only want the filtered data to be included?