SUMIFS in VBA using named ranges

Question

I have been struggling with a component of a VBA function. I need to use SUMIFS to sum values from a dataset with named ranges. I really can't see where I am going wrong but I suspect it is something to do with the named ranges.

Variables:

ACT = an actual results flag - if "ACT" then sum a certain range, else sum data from a range of sheets ("SUMSheets")
ML = Month lookup - JAN, FEB etc - this is the defined sum range
VER = Version lookup value - could be ACT21 or ACT22 etc - this looks up against the named range VERSION
AC = account number (a string) lookup value - this looks up against the ACCT named range

Named ranges:

VERSION - the named range that VER looks up against (i.e. Data!$F$2:$F$1048576)
ACCT - account number named range that AC looks up against (similar to VERSION)
SUMSheets - used in the else portion of the code and defines the names of the sheets to look up (e.g. INPUT, COSTS and SALES but could be numerous sheets)

Code that I have thus far is below. Thank you for any help you can provide.

Function NewfTBCalc(ACT As String, ML As String, VER As String, AC As String) As Double

Dim RangeName As String
Dim SumRange As String
Dim CalcValue As Double

If ACT = "ACT" Then
    With Application.WorksheetFunction
        CalcValue = .SumIfs(.indirect(ML), Range("VERSION"), VER, Range("ACCT"), AC)
    End With
Else  
    With Application.worksheetfuction
        RangeName = "'" & SUMSheets & "'!a:a"
        
        SumRange = "'" & SUMSheets & "'!" & .Substitute(.Address(1, .Column(), 4), "1", "") & ":" & .Substitute(.Address(1, .Column(), 4), "1", "")
        
        CalcValue = .SumProduct(.SumIf(.indirect(RangeName), AC, SumRange))
    
    End With
End If

NewfTBCalc = CalcValue

End Function

SUMIFS in VBA using named ranges

0 Answers