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

[alisoncleverly]

Hi everyone,

Here is my codes:

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

However I keep having error messages saying "Run-time Error 13 Type Mismatch".

Link to Sample

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

1. B3:K20 for Year 2020
2. B22:K39 for Year 2021
3. B41:K58 for Year 2022
4. 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

Below is a print screen of "Snapshot" sheet of which, the total number of models should be populated to the correct cells, and then each cell in the "Total" row should be summing the values in each Category column (columns B - K)

I'm aware that I messed up the data types, still, I didn't know which one(s). Can you please advise how to adjust the codes to get the correct results and get rid of the error messages? I'm also not sure if the fixed sign "$" should be put before each range. Please advise.

Thanks a lot!!!

 

[jasonb75]

The criteria, crt1 and crt2 need to be single cells to use the formula in vba the way that you're trying.

This 'might' work, I haven't tested it because there is no sample file at the link in your post, edit these 3 lines in your code as shown below.

Set Crt1 = ThisWorkbook.Sheets("Snapshot").Range("$A$3")

Set Crt2 = ThisWorkbook.Sheets("Snapshot").Range("$B$1")

Application.WorksheetFunction.SumIfs(SumRgn, CrtRgn1, Crt1.Offset(i - 3, 0), CrtRgn2, Crt2.Offset(0, r - 2), CrtRgn3, Crt3)

 

[alisoncleverly]

The criteria, crt1 and crt2 need to be single cells to use the formula in vba the way that you're trying.

This 'might' work, I haven't tested it because there is no sample file at the link in your post, edit these 3 lines in your code as shown below.

Set Crt1 = ThisWorkbook.Sheets("Snapshot").Range("$A$3")

Set Crt2 = ThisWorkbook.Sheets("Snapshot").Range("$B$1")

Application.WorksheetFunction.SumIfs(SumRgn, CrtRgn1, Crt1.Offset(i - 3, 0), CrtRgn2, Crt2.Offset(0, r - 2), CrtRgn3, Crt3)

Hi Jason,

Thanks a lot for your suggestion, my codes work!!! Thanks again