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

 

[Firefly2012]

Can you post the actual VLOOKUP formula you are using because from your sample data it isn't clear to me what you are trying to do. It would be a good idea to use one of the html makers (there's a link at the top of the forum) to post some sample data so your problem is clearly presented.

 

[Robert Mika]

Depends on your version of Excel you can wrap your formula with IFERROR(Excel 07/10) or ISERROR(Excel <07) but as Firefly2012 suggestes yif your post your data +formula there could be a better solution

 

[Bhugz]

Can you post the actual VLOOKUP formula you are using because from your sample data it isn't clear to me what you are trying to do. It would be a good idea to use one of the html makers (there's a link at the top of the forum) to post some sample data so your problem is clearly presented.

The formula I am using is =SUM(VLOOKUP(M5,'Game 1'!A:I,2,FALSE),(VLOOKUP(M5,'Game 2'!A:I,2,FALSE)))

Game 1

Player	Runs	Balls Faced	
Bugs	13	14	
Danny	9	13	
Umesh	-3	8	
Nick	4	10

Game 2

Player	Runs	Balls Faced
Bugs	18	13	
Nipun	13	13	
Shiv	-6	12	
Umesh	6	12

Overall Stats

Player	Innings	Runs	Balls Faced
			
Bugs	2	31	27
Danny	2	21	25
Umesh	2	3	20
Nick	1	#N/A	#N/A

For Innings in Overall stats I am using =COUNT(VLOOKUP(M5,'Game 1'!A:I,2,FALSE),(VLOOKUP(M5,'Game 2'!A:I,2,FALSE))) which works fine.

 

[Robert Mika]

Try like this:
Make a list of your sheets(in this example in cells I2 and I3 you can do as many as you need and then):

Excel Workbook
	A	B	C
1	Player	Runs	Balls Faced
2	Bugs	13	14
3	Danny	9	13
4	Umesh	-3	8
5	Nick	4	10
Game 1

Excel Workbook
	A	B	C
1	Player	Runs	Balls Faced
2	Bugs	18	13
3	Nipun	13	13
4	Shiv	-6	12
5	Umesh	6	12
Game 2

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	2	9	13	*	*	*	*	Game 2
4	Umesh	2	3	20	*	*	*	*	*
5	Nick	1	4	10	*	*	*	*	*
6	Nipun	*	13	13	*	*	*	*	*
7	Shiv	*	-6	12	*	*	*	*	*
Overall Status

Drag formulas down.
How the Innings are being counted?(is this cricket?basebal?)
Advice: do not use full column ranges as this very unefficient way and you probably do not need range A:I if you column index number for VLOOKUP is 2.

 

[Bhugz]

Cool, will try that. Yeah the Innings refers to the number of games played. It is indoor cricket.

So just to clarify, In the list of sheets column, for Bugs in I2 I'd have Game 1, Game 2, Game 3... etc.

 

[Robert Mika]

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

 

[Bhugz]

Wish I could understand what you did. Sorry I'm a noob when it comes to advanced excel formulas.

 

[Bhugz]

Made sense of it all.

Thanks heaps for your help. One more question though.

What formula would I use if I wanted to add the highest and lowest score for each player?

 

[Bhugz]

Is anyone able to help me with the formula for getting max and min values based on the above posts, please.


Thanks