I know Im not doing this properly but I am a VBA beginner and am trying to perform a sumif of a value on multiple sheets in a workbook and then return a sum of those sumifs.

This is how I tried but I get a #NAME? error and don't know where to go from here.

```
Public Function Lookup(x As Long) As Long

Dim sh As Worksheet, wb As Workbook, arr() As Variant

Set wb = Workbooks.Open("Purchased.xlsm")

For Each sh In wb
    arr() = Application.SumIf(Range("A1:A10000"), x, Range("B1:B10000"))
Next

Lookup = Application.WorksheetFunction.Sum(arr())

End Function

```

I have been unable to get this working and it returns a #VALUE! error but I think this may be closer to what I need:

```
Public Function AddItUp(x As Long) As Long
Dim sh As Worksheet, wb As Workbook, RunningSum() As Variant
Dim i As Long, j As Long
Set wb = Workbooks.Open("Week1Input.xlsm")
j = wb.Worksheets.Count
ReDim RunningSum(1 To j)
For i = 1 To j
    RunningSum(i) = Application.SumIf(wb.sh(i).Range("A1:A10"), x, wb.sh(i).Range("B1:B10"))
Next i
AddItUp = Application.WorksheetFunction.Sum(RunningSum)
End Function

```