Sum Vlookup result across several sheets

craigey1

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

Some videos you may like

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,991
Office Version
  1. 2019
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
252
Office Version
  1. 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
18
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
10,991
Office Version
  1. 2019
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
18
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
10,991
Office Version
  1. 2019
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,487
Members
410,685
Latest member
chandraganji
Top