 # Sum of Sumif across multiple sheets using VBA

• Hi,

I'm trying to do a sum of sumif across multiple sheets.
This is the formula I have to do the sum of sumif's with the sheet names in Sheets!\$C\$1:\$AM\$1:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets!\$C\$1:\$AM\$1&"'!A:A"),\$A4,INDIRECT("'"&Sheets!\$C\$1:\$AM\$1&"'!"&F\$1)))

However the sheets list wont always be too AM1, it may extend further, or less. I have tried to adjust the formula to make it dynamic but I keep getting #REF! using the following.

Doing some research on this I was able to find the following vba code, but when I enter the function in a cell it is giving #VALUE!, but something like this would work I think.

Code
``````Public Function AddItUp(x As Long) As Long
Dim RunningSum() As Variant
Dim i As Long, j As Long
j = ActiveWorkbook.Worksheets.Count
ReDim RunningSum(3 To j)
For i = 3 To j
RunningSum(i) = Application.SumIf(Worksheets(i).Range("A:A"), x, Worksheets(i).Range("e:e"))
Next i
End Function``````

Many thanks

• Re: Sum of Sumif across multiple sheets using VBA

What are the sum ranges? It sounds like each worksheet can have a variable number of headers but what are the hooks?

• Re: Sum of Sumif across multiple sheets using VBA

The sum ranges are cols E-M in the look up sheets, but only one is summed at a time. In the formula I posted above the sum range is specified by the second indirect to cell F1

• Re: Sum of Sumif across multiple sheets using VBA

Are you interested in keeping this formula driven? If not then where is the criteria? Are the sheets all set up the same? It would be great if you could just post a sample.

• Re: Sum of Sumif across multiple sheets using VBA

No if possible I would prefer to do it using VB. In my original post I have shown some code I found from a past post where someone was looking for something similar, but I couldn't get it to work. If someone could work off of that for a solution that would be great.

Ok so I have attached a similar example of my spreadsheet.

I have existing VB code that populates the list of vendors in 'Summary' and the list of sheets in the workbook in 'Sheets'. The number and names of the sheets in the workbook will vary.

In 'Summary' B1:M1 there is an example of the formula I have currently that works, but only if you manually enter the reference to the list of worksheets (Sheets!\$C\$1:\$E\$1).

I'm looking to either amend the formula so that that reference is dynamic depending on the number of entries in 'Sheets' row 1, or use VB to do it.

Any help is much appreciated.

## Files

• Re: Sum of Sumif across multiple sheets using VBA

See if this works for you

## Files

• Re: Sum of Sumif across multiple sheets using VBA

Thanks but I can't get it to work. For example, on the summary sheet in a cell I type '=sumInvoice(A4)' in cell C4 (I also tried referencing other cells) and get #VALUE!, but I'm not sure if I'm trying it in the correct way.

• Re: Sum of Sumif across multiple sheets using VBA

The function takes two arguments. See the example for details.

• Re: Sum of Sumif across multiple sheets using VBA

Having done some more research I have the following code, which is almost working for me. I think if the sum range (in red) can be changed to be an indirect look to row 1 and which ever column the Cell is currently using as the criteria it will work.

I've attached an updated spreadsheet with the code in it.

Any help with that would be awesome

Sub AssessSummary2()
Dim ws As Worksheet, MyArr1 As Range, cell As Range
Dim LR As Long, i As Long, MyArr2
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Summary").Activate
Set MyArr1 = Range("A3:A" & LR)
Set MyArr3 = Range("A3:M" & LR)
ReDim MyArr2(0 To MyArr3.Cells.Count)
For Each ws In Worksheets
i = 0
If ws.Name <> "Summary" Then
For Each cell In MyArr1
MyArr2(i) = MyArr2(i) + WorksheetFunction.SumIf(ws.Range("A:A"), cell, ws.Range("f:f"))
i = i + 1
Next cell
End If
Next ws
Range("b3:m" & LR) = Application.Transpose(MyArr2)
End Sub

## Files

• Re: Sum of Sumif across multiple sheets using VBA

you have no idea what I posted for you do you? Good luck.

• Re: Sum of Sumif across multiple sheets using VBA