Sum Vlookup result across several sheets

craigey1

New Member
Joined
Apr 6, 2020
Messages
20
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 15, 2019
Messages
297
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Apr 6, 2020
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
@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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 6, 2020
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
=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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,548
Members
430,295
Latest member
amdis

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
Top