Sum Vlookup result across several sheets

[craigey1]

I'm trying to sum up the vlookup results of several sheets (all with the same layout). I can get the vlookup to work if I specify the sheet name & record each sheets result in a new column, but I've been trying to use indirect with a list of the sheets, but not having any luck so far.

My score sheet looks like this & I'm trying to Sum up the Round point / Column O across several sheets. I'm using a vlookup as the people named in column A will vary.

Scoresheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Round 1		Round 2		Round 3		Round 4		Total		Position / Rank	Round Position	Round Points
2		Name	Bells	Score	Bells	Score	Bells	Score	Bells	Score	Bells	Score
3	1	Mic	3	5	2	4	1	3	4	6	10	18	10	Second	2
4	2	Mel	2	4	1	5	2	2	3	4	8	15	7		0
5	3	Mary	2	3	2	5	4	5	2	5	10	18	10	Second	2
6	4	John	1	5	1	3	2	4	0	2	4	14	4		0
7
8	1	Clive	3	4	2	2	2	2	2	2	9	10	2		0
9	2	Fred	1	5	0	3	2	3	3	4	6	15	6		0
10	3	Bob	2	5	2	2	4	5	2	5	10	17	9	Third	1
11	4	Mo	2	3	0	5	0	2	2	5	4	15	5		0
12
13	1	Mark	1	3	1	1	2	5	3	4	7	13	3		0
14	2	Gary	3	4	1	2	1	1	1	2	6	9	1		0
15	3	Harry	2	6	2	3	2	6	2	6	8	21	12	First	3
16	4	jerry	2	3	2	5	2	4	2	4	8	16	8		0
Shoot_6

If I use the formula below I can record each persons result from the sheet specified.

=(IFNA(VLOOKUP($A3,Shoot_6!$B$3:$O$31,14,0),0))

& I can get all the scores summed up if I manually specify all the sheets, (but this gets quite complex & prone to mistakes if there are lots of sheets)

=SUM(IFNA(INDEX(Shoot_1!$B$3:$O$31,MATCH(A3,Shoot_1!$B$3:$B$31,0),14),0),IFNA(INDEX(Shoot_2!$B$3:$O$31,MATCH(A3,Shoot_2!$B$3:$B$31,0),14),0),IFNA(INDEX(Shoot_3!$B$3:$O$31,MATCH(A3,Shoot_3!$B$3:$B$31,0),14),0))

I've tried using SUM / INDEX / MATCH / INDIRECT but not having any luck (Note SheetList is a worksheet containing the names of the sheets in A1:A7)

=SUM(INDEX(INDIRECT(SheetList!$A$1:$A$7&"!$B$3:$O$31"),MATCH(A3,INDIRECT(SheetList!$A$1:$A$7&"!$B$3:$B$31"),0),14))

 

[jasonb75]

Perhaps

=SUM(SUMIF(INDIRECT(Sheetlist!$A$1:$A$7&"!$B$3:$B$31"),$A3,INDIRECT(Sheetlist!$A$1:$A$7&"!$O$3:$O$31")))

Must be array confirmed with Ctrl Shift Enter otherwise you'll see a #VALUE! error.

 

[mehidy1437]

This might be handy if cells are identical in all sheets =SUM('*'!O3)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Round 1		Round 2		Round 3		Round 4		Total		Position / Rank	Round Position	Round Points
2		Name	Bells	Score	Bells	Score	Bells	Score	Bells	Score	Bells	Score
3	1	Mic	3	5	2	4	1	3	4	6	10	18	10	First	9	=SUM('*'!O3)
4	2	Mel	2	4	1	5	2	2	3	4	8	15	7	First	9	=SUM('*'!O4)
5	3	Mary	2	3	2	5	4	5	2	5	10	18	10	First	9	=SUM('*'!O5)
6	4	John	1	5	1	3	2	4	0	2	4	14	4	First	9	=SUM('*'!O6)
Sheet1

Cell Formulas
Range	Formula
K3:L6	K3	=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
M3:M6	M3	=IF(ISBLANK($L3),"",RANK.EQ($L3, $L$3:$L$31,1)+COUNTIFS($L$3:$L$31, $L3, $K$3:$K$31, "<" & $K3))
N3:N6	N3	=IF(ISBLANK($L3),"",LOOKUP(SUM(IF($M3<$M$3:$M$31,1/COUNTIF($M$3:$M$31,$M$3:$M$31)))+1,{1,2,3,4},{"First","Second","Third",""}))
O3:O6	O3	=SUM(Sheet2:Sheet4!O3)

 

[craigey1]

@jasonb75
That's brilliant thanks - (I forgot to mention that A3 was the column containing the name in the summary sheet)

@mehidy1437
Sorry - I'm not sure I follow. I don't know why you've put he the round points / position as the same values. The summary sheet I'm referring to is a different sheet to summaries the weekly Round points that have been earned over the course of a year.


Just a quick follow up though, How could I handle blank entries in the Sheetlist? Thanks.

 

[jasonb75]

How could I handle blank entries in the Sheetlist?

This should work, array confirmed as before.

=SUM(IFERROR(SUMIF(INDIRECT(Sheetlist!$A$1:$A$7&"!$B$3:$B$31"),$A3,INDIRECT(Sheetlist!$A$1:$A$7&"!$O$3:$O$31")),0))

 

[craigey1]

=SUM(IFERROR(SUMIF(INDIRECT(Sheetlist!$A$1:$A$7&"!$B$3:$B$31"),$A3,INDIRECT(Sheetlist!$A$1:$A$7&"!$O$3:$O$31")),0))

Perfect, thanks - I thought of the IFERROR, but was trying to insert it just before the sheetlist - i.e. INDIRECT(IFERROR(Sheetlist!

 

[jasonb75]

I thought of the IFERROR, but was trying to insert it just before the sheetlist - i.e. INDIRECT(IFERROR(Sheetlist!

I can see why you might think that but at that stage it would just be a blank cell (which is not an error).