Posts by gt213

Re: Sum of Sumif across multiple sheets using VBA

Sorry Bryce I hadn't seen your attachment.

I've moved the code to my actual workbook and it is working, so thanks for your help on this!

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

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

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

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

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.

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