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.


    =SUMPRODUCT(SUMIF(INDIRECT("'"&"Sheets!$C$1:"&ADDRESS(1,COUNTA(Sheets!1:1))&"'!A:A"),$A4,INDIRECT("'"&Sheets!$C$1:$AM$1&"'!"&D$1)))



    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
     AddItUp = Application.WorksheetFunction.Sum(RunningSum)
     End Function



    Many thanks

  • 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


    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.

  • Re: Sum of Sumif across multiple sheets using VBA


    See if this works for you


  • 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


    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!