How to correct my combined SumIf and SumProduct calculation in macro coding

I was summing a column that was all Canadian funds but the client has since introduced other currencies into the rows. So now to sum a column if it is canadian funds then I just sum the funds and if it is other than CDN funds then I need to multiply by the currency then sum it all as Canadian. Here is my code below. Can anyone tell me why the SumProduct function will not work properly in my code. If I do the following command in Excel columns then everything works fine but now I need to take this code

SUMIF($I$2:$I$6,"=CDN",P2:P6)+SUMPRODUCT(P2:P6,$J$2:$J$6)

and incorporate it in my Excel Macro code where I look thru worksheets to sum each individual column that has numerical amounts. Am doing this to update the summary total worksheet. Here is my Excel Macro code below. I get a compile error on my new SummaryTotal field stating that "Sub or Function not defined" and it highlights the SumProduct function within my NEW SummaryTotal field calculation.

The old SummaryTotal calculation worked just fine but when foreign currencies were introduced into the rows I had to modify the calculation. I did not want to loop thru the columns manually using a loop because each worksheet has maybe 30 columns that need to be individually summed and there are maybe 20 worksheets. My logic loops thru each worksheet and then summarizes the dollar totals for each individual column to a specific cell within the YTD summary total worksheet.

How do I this modified SummaryTotal calculation to do what I need to do? Here is my macro coding below.

' Set up Start Column and end column to loop thru on YTD_Summary Worksheet
    StartColumn = "C3"
    EndColumn = "P3"
    
    ' Set up Start Row and End Row to loop thru on YTD_Summary Worksheet
    StartRow = 3
    EndRow = YTDSummarySheet.Range("YTDSummary_Accts_Footer").Row() - 1
    
    ' Loop thru the YTD_Summary Cost Centre columns
    For Each ColIndex In YTDSummarySheet.Range(StartColumn & ":" & EndColumn).Cells
        Current_Cost_Centre = YTDSummarySheet.Cells(2, ColIndex.Column).Value
        TempWork_Sheet = "CC_" & Current_Cost_Centre
        Set InvoiceLogSheet = Worksheets(TempWork_Sheet)
        lastInvoiceLogLine = InvoiceLogSheet.Range("CC_Footer_" & Current_Cost_Centre).Row()

        'Loop thru the YTD_Summary Object.Sub rows
        For RwIndex = StartRow To EndRow
            WorkObjSub = YTDSummarySheet.Cells(RwIndex, 1).Value
            
            ' If object/sub starts with "8" then
            ' look up object_Sub account to get appropriate column
            If Mid(WorkObjSub, 1, 1) = "8" Then
                ColumnLetter = CheckForOBjSubColumn(WorkObjSub)
            
                If ColumnLetter = "?" Then
                    Response = MsgBox("Object/Sub " & WorkObjSub & " cannot be found on CONTROL worksheet, Contact someone in ITS. Save of Invoice CANNOT continue.", vbOkay, "Calc Summary")
                    Exit Sub
                End If
            
                If ColumnLetter = "" Then
                    Response = MsgBox("Object/Sub: " & WorkObjSub & " in CONTROL worksheet has NO VALUE in Object Column.  Contact someone in ITS. Save of Invoice CANNOT continue.", vbOkay, "Calc Summary")
                    Exit Sub
                End If
            Else
                ' Use the string stored in the cell to look up
                ' tax account so the tax column can be returned
                WorkTaxName = WorkObjSub
                ColumnLetter = CheckForTaxColumn(WorkTaxName, "SentAcct-WantCol")
                        
                ' Check to see if a tax Column was returned
                If ColumnLetter = "?" Then
                   Response = MsgBox("Tax Account: " & WorkTaxName & " in CONTROL worksheet has NO VALUE in G/L Tax Account Column.  Contact someone in ITS. Save of Invoice CANNOT continue.", vbOkay, "Calc Summary")
                   Exit Sub
                End If
            End If
            
            ' Convert the Column Letter to a Column Number
            ' --------------------------------------------------
            Current_Cost_Centre = YTDSummarySheet.Cells(2, ColIndex.Column).Value
            TempWork_Sheet = "CC_" & Current_Cost_Centre
            Set sh = Sheets(TempWork_Sheet)
            ColumnNumber = sh.Cells(1, ColumnLetter).Column
            ColumnNumber_Currency = sh.Cells(1, "I").Column
            ColumnNumber_Exchange = sh.Cells(1, "J").Column
            
            ' Summarize a specific column for a specified Cost Centre
            '
            ' Without the Cells property being specifically qualified,
            ' the SUM method will apply only to the active sheet.
            '
            ' The Sum logic had to be slightly modfified to use a SumIf and SumProduct
            ' because we want to sum each column in total Canadian funds for that column.
            ' Any USD funds must be first converted to Canadian funds then summed together.
            '
            '=SUMIF($I$2:$I$6,"=CDN",P2:P6)+SUMPRODUCT(P2:P6,$J$2:$J$6)
            ' = SUMIF(CurrencyColRange,"=CDN",SumCurrentColRange) + SumProduct(CurrentColRange,ExchangeColRange)
            ' ---------------------------------------------------
            '(old command)  SummaryTotal = Application.WorksheetFunction. _
                                            Sum(Range(sh.Cells(2, ColumnNumber), sh.Cells(lastInvoiceLogLine, ColumnNumber)))
            
            SummaryTotal = Application.WorksheetFunction. _
                            SumIf(Range(sh.Cells(2, ColumnNumber_Currency), sh.Cells(lastInvoiceLogLine, ColumnNumber_Currency)), "=CDN", _
                            Range(sh.Cells(2, ColumnNumber), sh.Cells(lastInvoiceLogLine, ColumnNumber))) + _
                            SumProduct(Range(sh.Cells(2, ColumnNumber), sh.Cells(lastInvoiceLogLine, ColumnNumber)), _
                            Range(sh.Cells(2, ColumnNumber_Exchange), sh.Cells(lastInvoiceLogLine, ColumnNumber_Exchange)))
            
            YTDSummarySheet.Cells(RwIndex, ColIndex.Column).Value = SummaryTotal
        
        Next RwIndex
    Next ColIndex
        
    Range("YTDSummary_Title").Value = Range("FiscalYear_string").Value & " Year to Date Summary of each Cost Center by Column"
        
    Application.Calculate

Came across this URL regarding SUMPRODUCT. Will this help what I am trying to do with the combination of SUMIF and SUMPRODUCT?

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA SUMPRODUCT and Worksheet SUMPRODUCT do not execute in quite the same way... generally speaking using SUMPRODUCT in VBA is done via Evaluate.

SummaryTotal = Application.WorksheetFunction. _
                            SumIf(Range(sh.Cells(2, ColumnNumber_Currency), sh.Cells(lastInvoiceLogLine, ColumnNumber_Currency)), "=CDN", _
                            Range(sh.Cells(2, ColumnNumber), sh.Cells(lastInvoiceLogLine, ColumnNumber))) + _
                            SumProduct(Range(sh.Cells(2, ColumnNumber), sh.Cells(lastInvoiceLogLine, ColumnNumber)), _
                            Range(sh.Cells(2, ColumnNumber_Exchange), sh.Cells(lastInvoiceLogLine, ColumnNumber_Exchange)))
YTDSummarySheet.Cells(RwIndex, ColIndex.Column).Value = SummaryTotal

might be converted to:

With sh
    With .Range(.Cells(2,ColumnNumber_Currency),.Cells(lastInvoiceLogLine,ColumnNumber_Currency))
        v = .Parent.Evaluate("SUMIF(" & .Address & ",""CDN""," & .Offset(,7).Address & ")")
        v = v + .Parent.Evaluate("SUMPRODUCT(" & .Offset(,7).Address & "," & .Offset(,1).Address & ")")
    End With
End With
YTDSummarySheet.Cells(RwIndex, ColIndex.Column).Value = v

The above assumes that Col J is 0 or blank where Col I is CDN else you would be double counting CDN. If Column J were set to 1 for CDN entries then you could remove the SUMIF altogether.
You don't need to use Evaluate with SUMIF of course but for sake of consistency that was adopted approach.

Note: above untested (no sample file nor time to create one!)

Thanks for your reply, will try it out.

I get a type mismatch error on the following code line. I have no idea what this offset and Address is doing in this code so maybe you could explain. I do not fully understand what you are doing.

v = v + .Parent.Evaluate("SUMPRODUCT(" & .Offset(,7).Address & "," & .Offset(,1).Address & ")")

I attached my spreadsheet logic. Look at the following method where I am trying to modify the code:

Private Sub CalcYTDSummary()

I'm afraid I don't have the time to look in depth but at first glance I suspect the issue is down to your blank sheets

You could conduct a quick fix to handle all errors...

Dim v As Variant, vTemp As Variant
v = .Parent.Evaluate("SUMIF(" & .Address & ",""CDN""," & .Offset(,7).Address & ")")
If IsNumeric(v) Then 
     vTemp = v
End If
v = .Parent.Evaluate("SUMPRODUCT(" & .Offset(,7).Address & "," & .Offset(,1).Address & ")")
If IsNumeric(v) Then
    vTemp = vTemp + v
End If
YTDSummarySheet.Cells(RwIndex, ColIndex.Column).Value = vTemp

How would I change ".Offset(,7)" to be a different column each time thru? I want "Offset(,7)" to be "Column_Number" Instead. Am going to play with this logic. The logic works but it stays in the same column to do the summing. So I need to take ".Offset(,7)" and replace it with "Column_Number" which in my code changes each time thru.

Each time I loop thru my columns it keeps re-adding the same column because I have Offset(,7) Is there a way to change Offset(,7) to a variable value. I have a field called Column_Number and this contains the current coloumn number of the column I want to add. I tried to modify this OFFSET logic above without much sucess. The logic you gave me works but I am currently always adding the exact same column.

Right now I am searching on the web for an example of a variable offset.

I changed the code slightly to the following and now it works the way I need it to work. I replaced the column offset with my variable ColumnNumber because this is the column where I always want to be to sum values. This variable value keeps changing as I loop thru my worksheet. Because the code is using the Offset function I had to subtract 9 from the variable ColumnNumber because the Offset assumes you are starting from the Currency column. I want the column number to be the value that is in ColumnNumber. Please let me know if you see anything wrong with my code before I mark it as resolved.

            v = 0
            vTemp = 0
            
            With sh
               With .Range(.Cells(2, ColumnNumber_Currency), .Cells(lastInvoiceLogLine, ColumnNumber_Currency))
                    ' Had to subtract "9" from ColumnNumber to start at proper offset column
                    v = .Parent.Evaluate("SUMIF(" & .Address & ",""CDN""," & .Offset(, ColumnNumber - 9).Address & ")")

                    If IsNumeric(v) Then
                        vTemp = v
                    End If
                    
                    ' Had to subtract "9" from ColumnNumber to start at proper offset column
                    v = .Parent.Evaluate("SUMPRODUCT(" & .Offset(, ColumnNumber - 9).Address & "," & .Offset(, 1).Address & ")")

                    If IsNumeric(v) Then
                        vTemp = vTemp + v
                    End If

                    YTDSummarySheet.Cells(RwIndex, ColIndex.Column).Value = vTemp
               End With
            End With