SUMIF(VLOOKUP...) Help

F

Firefly2012

Well-known Member

Joined

Dec 28, 2011

Messages

3,638

• Mar 26, 2012

• #2

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.

 

Upvote 0

Robert Mika

MrExcel MVP

Joined

Jun 29, 2009

Messages

7,256

• Mar 26, 2012

• #3

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

 

Upvote 0

B

Bhugz

New Member

Joined

Jul 13, 2011

Messages

11

• Mar 26, 2012

• #4

Firefly2012 said:

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.

Click to expand...

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

HTML:

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

Game 2

HTML:

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

Overall Stats

HTML:

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.

 

Upvote 0

Robert Mika

MrExcel MVP

Joined

Jun 29, 2009

Messages

7,256

• Mar 26, 2012

• #5

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.

 

Upvote 0

B

Bhugz

New Member

Joined

Jul 13, 2011

Messages

11

• Mar 26, 2012

• #6

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.

 

Upvote 0

Robert Mika

MrExcel MVP

Joined

Jun 29, 2009

Messages

7,256

• Mar 26, 2012

• #7

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

 

Upvote 0

B

Bhugz

New Member

Joined

Jul 13, 2011

Messages

11

• Mar 26, 2012

• #8

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

 

Upvote 0

B

Bhugz

New Member

Joined

Jul 13, 2011

Messages

11

• Mar 26, 2012

• #9

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?

 

Upvote 0

B

Bhugz

New Member

Joined

Jul 13, 2011

Messages

11

• Apr 2, 2012

• #10

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


Thanks

 

Upvote 0