SUMIF(VLOOKUP...) Help

[Bhugz]

Hey folks,
I have been put in charge with doing my teams indoor cricket stats after each game. I have used a =SUM(VLOOKUP... combo for the first 2 games but have ran into a problem. I have each game in a separate sheet within the same workbook, but I get a #N/A error when using the formula combo (see below) What formula combo can I use to fix this?
BATTING STATISTICS
Player Innings Runs

Bugs 2 31
Danny 2 21
Umesh 2 3
Nick 1 #N/A
Jeshal 2 20
Tyrone 1 #N/A
Divyang 1 #N/A


Thanks heaps

 

[sum_kon]

The "list" is a Named Range.
You can have it in any part of your work book and as many as you need and they are names of your worksheets.
Please see below the numbers of Innits formula:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Player	Innings	Runs	Balls Faced	*	*	*	*	List of Sheets
2	Bugs	2	31	27	*	*	*	*	Game 1
3	Danny	1	9	13	*	*	*	*	Game 2
4	Umesh	2	3	20	*	*	*	*	*
5	Nick	1	4	10	*	*	*	*	*
6	Nipun	1	13	13	*	*	*	*	*
7	Shiv	1	-6	12	*	*	*	*	*
Overall Status

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Dear frnd,
I'm having a same problem, i.e to add two different conditions in two different sheets and get a result based on criteria fixed in the answer sheet…<o></o>
<o> </o>
<o> </o>
The problem is as follows:-


A B C
Mark No Qty Code <o></o>

1002 1.55 M01A
1003 2.96 M02A
1004 0.56 M03A
1002 2.81 M01A
1002 2.87 M02A
1003 7.01 M02A
1004 1.04 M03A
.. ... ... ...
.. .... .... ..
like up to 14877 row<o></o>
<o> </o>
This is Sheet-1 (Data sheet) <o></o>


<o></o>

and the ans. will be in sheet-2 considering below condition:-

A B C D
Mark no M01A M02A M03A ....

1002 +1.55+2.81 +2.87 <o></o>
1003 +2.96+7.01
1004 +0.56+1.04
... ....<o></o>
Sheet-2 (result will be like this) …..<o></o>
<o> </o>
Means first it will look the value of “Mark No” from Sheet-1 column “A2:A14877” and then it will look corresponding “Code”, and after fulfilling that it will add all “Qty” related to said code+mark no and showing the result of summation in “Sheet-2” “B2” cell .<o></o>


Please help me to find out any formula either 'vlookup' or 'sum-if' or any easy/suitable...<o></o>
<o> </o>
Thanks in advance….
<o> </o>