VBA code for a conditional sumproduct function

Question

I am attempting to create a function in VBA that is calculating a conditional sumproduct().  The sumproduct in excel is written as =sumproduct(--(A5=$C$5:$C$400000),--(B5<$D$5:$D$400000)).  The function is designed to only calculate the sum product using cells within the two arrays that meet the conditions I specified.  However, b/c of the size of the arrays, excel takes roughly 1 hour to calculate the answers.  As a result, I was hoping to put the formula into VBA as a function to speed up the calculation time.  Can you please provide the VBA code?

Thank you

Answer 1

With that many rows, I'm not sure VBA will help, but try this:

Function MyCount(Val1, Val2) As Long

  Dim r As Long

  Dim m As Long

  m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

  For r = 5 To m

    If Cells(r, 3) =Val1 And Cells(r, 4) > Val2 Then

      MyCount = MyCount + 1

    End If

  Next r

End Function

Use as =MyCount(A5,B5)

If you want the function to recalculate automatically, add a line

Application.Volatile

at the beginning, but this might have a negative impact on performance.