Sum Vlookup result across several sheets

craigey1

New Member
Joined
Apr 6, 2020
Messages
30
Office Version
  1. 2019
Platform
  1. Windows
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
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)
 
Upvote 0
@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.
 
Upvote 0
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))
 
Upvote 0
=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!
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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?

Disable AdBlock

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
Back
Top