Excel VBA Error 6 Overflow with sumifs and calculation (math)

[EuginG]

I am trying to do some calculation with sumifs and criterion. I need to do SUMIFS of the first column (jRange) and to divide it by SUMIFS of the second column (lRange) several times (based on different criterion). And put a result to the certain cells.
I have found a solution for that (probably not the best, but it works). Just using / for dividing.
But.. It works only if I have necessary data for calculation in my Excel file. If a script based on my criterion cannot match the data then the whole script goes to Error 6 OVERFLOW.
Please help!

Sub test()

Dim Cell As Range, gRange As Range, lRange As Range, jRange As Range, mRange As Range

Set gRange = Range("G4:G5000")
Set lRange = Range("L4:L5000")
Set jRange = Range("J4:J5000")
Set mRange = Range("M4:M5000")

Range("S11").Value = Application. WorksheetFunction.SumIfs(jRange, gRange, 21, mRange, "OK", jRange, ">=10.5") / Application.SumIfs(lRange, gRange, 21, mRange, "OK", jRange, ">=10.5")
Range("T11").Value = Application. WorksheetFunction.SumIfs(jRange, gRange, 33, mRange, "OK", jRange, ">=16.5") / Application.SumIfs(lRange, gRange, 33, mRange, "OK", jRange, ">=16.5")

And so on..

End sub

 

[EuginG]

In addition.

I understand, that the error above is because of 0 / 0 division. But I do not know how to avoid it. Maybe there are some tricks?

 

[Domenic]

Try...

Sub test()

    Dim Cell As Range, gRange As Range, lRange As Range, jRange As Range, mRange As Range
    
    Set gRange = Range("G4:G5000")
    Set lRange = Range("L4:L5000")
    Set jRange = Range("J4:J5000")
    Set mRange = Range("M4:M5000")
    
    
[COLOR=#ff0000]    Dim dblNumerator As Double
    Dim dblDivisor As Double
    
    dblNumerator = Application.SumIfs(jRange, gRange, 21, mRange, "OK", jRange, ">=10.5")
    dblDivisor = Application.SumIfs(lRange, gRange, 21, mRange, "OK", jRange, ">=10.5")
    
    If dblDivisor <> 0 Then
        Range("S11").Value = dblNumerator / dblDivisor
    End If[/COLOR]
    
    'etc
    '
    '
    '

End Sub

Hope this helps!

 

[EuginG]

Yes, Domenic, it WORKS! Thank you!

 

[Domenic]

That's great, you're very welcome.

Cheers!