Excel VBA SUMIF Run-time error '424': Object Required

Question

This produces an Error: Run-time error '424': Object Required

Private Function Y_Mode() As Double
    Dim MaxGrad As Double
    MaxGrad = WorksheetFunction.Max(Graduation_Series)
    Y_Mode = WorksheetFunction.SumIf(Graduation_Series, MaxGrad, Y_Series)
End Function

It is failing on this line:

Y_Mode = WorksheetFunction.SumIf(Graduation_Series, MaxGrad, Y_Series)

Everything seems defined and instantiated properly and the elements of each array are of type Variant/Double

Watch : + :     Graduation_Series :  : Variant/Variant(0 to 14) : SU_Solution.Y_Mode
Watch :   :     MaxGrad : 474.281204765715 : Double : SU_Solution.Y_Mode    
Watch : + :     Y_Series :  : Variant/Variant(0 to 14) : SU_Solution.Y_Mode

I tried putting MaxGrad into a Range and then referencing the Range in SUMIF, but that did not work either.

seems like SumIf doesn't work with arrays. This formula gives me error in Excel Online =SUMIF({1,2},2,{3,4}) — Slai
The function should probably be taking ByVal graduationSeries As Range, ByVal ySeries As Range parameters instead of using some global (?) state. — Mathieu Guindon
Thanks for your feedback. I am going to go down the SUM(IF()) array formula path instead of converting to array to Range. — bravogolfgolf
SUM(IF()) does not work in VBA either so I had to "roll" my own. — bravogolfgolf

bravogolfgolf bravogolfgolf · Accepted Answer · 2017-03-10T02:25:27

You cannot use SUMIF with arrays.

The criteria that you can use with the SUMIF() worksheet function is limited to text, numbers, or a range, and the function cannot use array constants.

https://support.microsoft.com/en-us/help/275165/when-to-use-a-sum-if-array-formula

Excel VBA SUMIF Run-time error '424': Object Required

1 Answers