# Sum Vlookup result across several sheets

#### craigey1

##### New Member
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
ABCDEFGHIJKLMNO
1Round 1Round 2Round 3Round 4TotalPosition / RankRound PositionRound Points
2NameBellsScoreBellsScoreBellsScoreBellsScoreBellsScore
31Mic35241346101810Second2
42Mel241522348157 0
53Mary23254525101810Second2
64John151324024144 0
7
81Clive342222229102 0
92Fred150323346156 0
103Bob2522452510179Third1
114Mo230502254155 0
12
131Mark131125347133 0
142Gary34121112691 0
153Harry2623262682112First3
164jerry232524248168 0
Shoot_6

If I use the formula below I can record each persons result from the sheet specified.
Excel Formula:
``=(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)
Excel Formula:
``=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)
Excel Formula:
``=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))``

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### jasonb75

##### Well-known Member
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

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

Book1
ABCDEFGHIJKLMNOP
1Round 1Round 2Round 3Round 4TotalPosition / RankRound PositionRound Points
2NameBellsScoreBellsScoreBellsScoreBellsScoreBellsScore
31Mic35241346101810First9=SUM('*'!O3)
42Mel241522348157First9=SUM('*'!O4)
53Mary23254525101810First9=SUM('*'!O5)
64John151324024144First9=SUM('*'!O6)
Sheet1
Cell Formulas
RangeFormula
K3:L6K3=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
M3:M6M3=IF(ISBLANK(\$L3),"",RANK.EQ(\$L3, \$L\$3:\$L\$31,1)+COUNTIFS(\$L\$3:\$L\$31, \$L3, \$K\$3:\$K\$31, "<" & \$K3))
N3:N6N3=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:O6O3=SUM(Sheet2:Sheet4!O3)

#### craigey1

##### New Member
@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

##### Well-known Member

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

##### New Member
=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

##### Well-known Member
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).

Replies
10
Views
125
Replies
2
Views
127
Replies
17
Views
286
Replies
2
Views
161
Replies
13
Views
340