VBA - Run-time Error 13 Type Mismatch using WorksheetFunction.SumIFs

Question

Can you please advise how to fix the following code which uses SUMIFS? I managed to get them together from a lot of sources and I'm aware that there must be something wrong with the data types.

Sub snap2020()

'Declare a variable
    Dim wsOpps As Worksheet, wsSnapshot As Worksheet
    Dim i As Integer, r As Integer
    Dim CatSnapShot As Range

    Dim SumRgn As Range 'The desired Sum Range
    Dim CrtRgn1 As Range 'Range applied to Criteria 1
    Dim Crt1 As Range 'Criteria 1
    Dim CrtRgn2 As Range 'Range applied to Criteria 2
    Dim Crt2 As Range 'Criteria 2
    Dim CrtRgn3 As Range 'Range applied to Criteria 3
    Dim Crt3 As Range 'Criteria 3

    Set SumRgn = ThisWorkbook.Sheets("Opps tracker 2020").Range("T1:T2000")
    Set CrtRgn1 = ThisWorkbook.Sheets("Opps tracker 2020").Range("C1:C2000")
    Set Crt1 = ThisWorkbook.Sheets("Snapshot").Range("$A$3:$A$19")
    Set CrtRgn2 = ThisWorkbook.Sheets("Opps tracker 2020").Range("K1:K2000")
    Set Crt2 = ThisWorkbook.Sheets("Snapshot").Range("$B$1:$K$1")
    Set CrtRgn3 = ThisWorkbook.Sheets("Opps tracker 2020").Range("J1:J2000")
    Set Crt3 = ThisWorkbook.Sheets("Snapshot").Range("$A$2")

    Set wsOpps = ThisWorkbook.Sheets("Opps tracker 2020")
    Set wsSnapshot = ThisWorkbook.Sheets("Snapshot")


'Set up Message Box
    If MsgBox("Update Snapshot?", vbYesNo + vbQuestion + vbDefaultButton2, "Opportunity Snapshot 2020") = vbNo Then
        Exit Sub
    End If

'Turn off events
    Application.EnableEvents = False

'Clear old data in Worksheet Snapshot
    wsSnapshot.Range("B3:K20").ClearContents

'Apply SUMIFs and Update data
    For i = 3 To 19
        For r = 2 To 11
            wsSnapshot.Cells(i, r) _
                = Application.WorksheetFunction.SumIfs(SumRgn, CrtRgn1, Crt1, CrtRgn2, Crt2, CrtRgn3, Crt3)
        Next r
    Next i
'Turn on events
    Application.EnableEvents = True

End Sub

So the purpose of the code is to create a snapshot of the Total number per Model per Category in 2020, 2021, 2022, 2023, and so on. I wanted the results to be populated within the ranges:

B3:K20 for Year 2020
B22:K39 for Year 2021
B41:K58 for Year 2022
B60:K77 for Year 2023

The SumIfs statement is supposed to be like this:

=SUMIFS('Opps tracker 2020'!T1:T2000, 'Opps tracker 2020'!C1:C2000, 'Snapshot'!A3:A19, 
'Opps tracker 2020'!K1:K2000, 'Snapshot'!B1:K1, 'Opps tracker 2020'!J1:J2000)

The sum range I'd like is column T in the "Opps tracker 2020" sheet, this I limited to only the first 2000 rows for testing
SUMIFs searches for matching Category (column K) in "Opp tracker 2020" sheet that match the Category in "Snapshot" sheet (which is Range B1:K1)
SUMIFs searches for matching Model (column C) in "Opp tracker 2020" sheet that match the Model in "Snapshot" sheet (range A3:A19 for 2020, A22:A38 for 2021, A41:A57 for 2022, A60:A76 for 2023)
Then finally SUMIFs has to make sure the Year is 2020 when calculating for Snapshot's 2020 range and same for other years

Here is a print screen of my "Snapshot' sheet

I'm not in anyway a programmer so if you can also suggest a better way to put the codes together, please feel free. I really appreciate any help!

Please advise how to fix this issue. Thanks a lot!

The problem is Crt1 and Crt2 - I don't think you can use multi-cell ranges here. — BigBen
From the docs: Criteria1, criteria2... - One or more criteria in the form of a number, expression, cell reference, or text that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4. — BigBen

Domenic Domenic · Accepted Answer · 2020-03-06T23:52:26

In addition to providing you with the formula, I have moved your message box to the beginning of your macro, since everything is dependent on the response given by the user.

And, I have also moved the assignment of your worksheets before the assignments of your sum and criteria ranges, so that you can use those worksheet variables when assigning your sum and criteria ranges, etc.

Sub snap2020()

'Set up Message Box
    If MsgBox("Update Snapshot?", vbYesNo + vbQuestion + vbDefaultButton2, "Opportunity Snapshot 2020") = vbNo Then
        Exit Sub
    End If

'Declare a variable
    Dim wsOpps As Worksheet, wsSnapshot As Worksheet
    Dim i As Integer, r As Integer

    Set wsOpps = ThisWorkbook.Sheets("Opps tracker 2020")
    Set wsSnapshot = ThisWorkbook.Sheets("Snapshot")

    Dim SumRgn As Range 'The desired Sum Range
    Dim CrtRgn1 As Range 'Range applied to Criteria 1
    Dim CrtRgn2 As Range 'Range applied to Criteria 2
    Dim CrtRgn3 As Range 'Range applied to Criteria 3

    With wsOpps
        Set SumRgn = .Range("T1:T2000")
        Set CrtRgn1 = .Range("C1:C2000")
        Set CrtRgn2 = .Range("K1:K2000")
        Set CrtRgn3 = .Range("J1:J2000")
    End With

'Turn off events
    Application.EnableEvents = False

'Clear old data in Worksheet Snapshot
    wsSnapshot.Range("B3:K20").ClearContents

'Apply SUMIFs and Update data
    For i = 3 To 19
        For r = 2 To 11
            wsSnapshot.Cells(i, r).Value = Application.SumIfs(SumRgn, CrtRgn1, wsSnapshot.Cells(i, 1).Value, CrtRgn2, wsSnapshot.Cells(1, r).Value, CrtRgn3, wsSnapshot.Range("A2").Value)
        Next r
    Next i

'Turn on events
    Application.EnableEvents = True

End Sub

VBA - Run-time Error 13 Type Mismatch using WorksheetFunction.SumIFs

1 Answers