sum of column across all sheets

[Hashmeer1]

i'm trying to pull a sum of each column from all 13 sheets onto a summary sheet using a combination of sum and vlookup.

f(x): =SUM(VLOOKUP($A3,'Jan 16'!$A:B,2,0))+(VLOOKUP($A3,'Feb 16'!$A:B,2,0))+(VLOOKUP($A3,'Mar 16'!$A:B,2,0))+(VLOOKUP($A3,'Apr 16'!$A:B,2,0))+(VLOOKUP($A3,'May 16'!$A:B,2,0))+(VLOOKUP($A3,'Jun 16'!$A:B,2,0))+(VLOOKUP($A3,'Jul 16'!$A:B,2,0))+(VLOOKUP($A3,'Aug 16'!$A:B,2,0))+(VLOOKUP($A3,'Sep 16'!$A:B,2,0))+(VLOOKUP($A3,'Oct 16'!$A:B,2,0))+(VLOOKUP($A3,'Nov 16'!$A:B,2,0))+(VLOOKUP($A3,'Dec 16'!$A:B,2,0))+(VLOOKUP($A3,'Jan 17'!$A:B,2,0))

however my lookup value isn't available on all sheets, easier said: the text value in column a may only exist on 1 to 12 of the 13 sheets, which returns with #N/A since i'm trying to return a sum value of all 13 sheets.

i feel like i'm going about this in a very caveman fashion but someone once told me don't be afraid to be a beginner. any help you can provide would be greatly appreciated.

 

[My Aswer Is This]

If you want a sum of all values in all sheets.
Try using this Vba script.

Sub Sum_All_Sheets()
Application.ScreenUpdating = False
Dim ans As Long
Dim c As Range
ans = 0
    
    For i = 1 To Sheets.Count
    
    For Each c In Sheets(i).UsedRange
        If IsNumeric(c.Value) = True Then ans = c.Value + ans
    Next
Next
MsgBox "Total sum all sheets  " & ans
Application.ScreenUpdating = True
End Sub

 

[Domenic]

...but someone once told me don't be afraid to be a beginner...

...or ask for help. First, list your sheet names in a range of cells, let say G2:G14, then try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$14&"'!A:A"),$A3,INDIRECT("'"&$G$2:$G$14&"'!B:B")))

Actually, it looks like you want the sum range to be a relative reference...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$14&"'!A:A"),$A3,INDIRECT("'"&$G$2:$G$14&"'!"&CELL("address",B:B))))

Hope this helps!

 

[Hashmeer1]

you're the man domenic, it took me a minute to figure out your advise but that first code it killed it. wish i would've asked on here a long time ago rather than spending hours on youtube