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