worksheetfunction.averageifs error: type mismatch

[k_l_c]

Hello, need some help please. The following code works fine up to averageif command. the countifs works fine. worksheetfunction.average works fine, so it's obvs the criteria, but I can't figure out why. I will need to put Q criteria in as well, but for now, just year. Y(Year) will be from user input. For now i have it set to 2021 for testing. I have used named ranges from the wsResults. THere is no problem with this. Any comments appreciated

Public Sub AuditResults() 're-code as function taking response from msg box as parameter
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim Y As Integer
Dim LR As Integer
Dim RngYr As Range
Dim RngQ As Range

LR = wsData.Range("A2").CurrentRegion.Rows.Count
Set RngQ = wsData.Range("F2:F" & LR) '1,2,3,4
Set RngYr = wsData.Range("G2:G" & LR) '2020, 2021

'Y = InputBox("Results for what year? 4 digits!")
Y = 2021

'Worksheets("Results").Activate

Set ws = Worksheets("Results")

With ws
Range("N2").Value = Y

Dim RngAgeCatData As Range
Set RngAgeCatData = wsData.Range("K2:K" & LR) 'Categorical
Dim RngAgeData As Range
Set RngAgeData = wsData.Range("J2:J" & LR) 'Numeric

'Q1Q1
Range("Q1SC").Value = WorksheetFunction.CountIfs(RngYr, Y, RngQ, 1, RngAgeData, "LT 60 Years")
Range("Q1SC").Offset(1, 0) = WorksheetFunction.CountIfs(RngYr, Y, RngQ, 1, RngAgeCatData, "Sixty1 - 70 Years")
Range("Q1SC").Offset(2, 0) = WorksheetFunction.CountIfs(RngYr, Y, RngQ, 1, RngAgeCatData, "Seventy1-80 Years")
Range("Q1SC").Offset(3, 0) = WorksheetFunction.CountIfs(RngYr, Y, RngQ, 1, RngAgeCatData, "Eighty1-90 Years")
Range("Q1SC").Offset(4, 0) = WorksheetFunction.CountIfs(RngYr, Y, RngQ, 1, RngAgeCatData, "Ninety1-100 Years")
Range("Q1SC").Offset(5, 0) = WorksheetFunction.CountIfs(RngYr, Y, RngQ, 1, RngAgeCatData, "O 100 Years")
Range("Q1TotQ1").Formula = "=SUM(R[-6]C:R[-1]C)"

Dim RngCAge As Range
Dim ValCAge As Range
Set RngCAge = ws.Range("Q1Q1_pc") '

For Each ValCAge In RngCAge
If Range("Q1TotQ1") <> "0" Then
ValCAge = ValCAge.Offset(0, -1) / ws.Range("Q1Q1_pc") * 100
ValCAge.Value = WorksheetFunction.Round(ValCAge.Value, 1)
Else: ValCAge = "0.0"
End If
ValCAge.NumberFormat = "##0.0"
Next

Dim test As Double
'Range("Q1AvgQ1").Value = WorksheetFunction.AverageIfs(RngAgeData, RngYr, Y, RngQ, 1)

test = WorksheetFunction.AverageIf(RngAgeData, RngYr, 2021)

 

[k_l_c]

ok.....got that to work with =WorksheetFunction.AverageIfs with one criteria (Year as Y) but still not accepting second criteria ..RngQ,1) in vba
error now: "Unable to get the AverageIfs property from the WorksheetFunction Class
formula works on a spreadsheet in a cell

 

[k_l_c]

i'm a ********.........there's no data for Q1. works fine for other Q