# 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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### 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
3
Views
100
Replies
5
Views
97
Replies
0
Views
168
Replies
0
Views
97
Replies
0
Views
78

1,181,606
Messages
5,930,866
Members
436,764
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back