Sumproduct with Greater Than Less Than

Hi All,

In column K, I am calculating a Sumproduct based on the criteria in columns C, D, & G. The data in all three should match. Column I has the numeric data. If column C, D, & G match and the cumulative value in Column I is greater than $1000 or less than $-1000 then I would like to return "Out of Tolerance", otherwise, "" (leave cell blank).

The code below works but takes an extraordinary amount of time to run because of the massive amounts of data I need. Can you help? Thanks for your time

Dim myLastRowA As Long
myLastRowA = Range("A65536").End(xlUp).Row

Range("k2").Select
ActiveCell.FormulaR1C1 = "=IF(SUMPRODUCT(--(R1C3:R65000C3=RC[-8]),--(R1C4:R65000C4=RC[-7]),--(R1C7:R65000C7=RC[-4]),R1C9:R65000C9)>=1000,""Out of Tolerance"",IF(SUMPRODUCT(--(R1C3:R65000C3=RC[-8]),--(R1C4:R65000C4=RC[-7]),--(R1C7:R65000C7=RC[-4]),R1C9:R65000C9)<=-1000,""Top"",""""))"

Range("k2").AutoFill Destination:=Range("k2:k" & myLastRowA)
Range("k2:k" & myLastRowA).Select

I do have questions about your implementation. Why are you doing the SUMPRODUCT twice? Could you not go =IF(ABS(SUMPRODUCT(..))>1000, ... , ...)? (That might half the time -- not sure how MS implemented things when you use the same formula twice)

Second, for column K, you're finding the number of rows, but for columns C,D,G you're saying the limit is 65,000. (Seems to me there is the potential for missed data there)

My suggestion:

Basically, it's not to use SUMPRODUCT. You're repeating multiple calculations, so you can do it smarter than SUMPRODUCT can because it doesn't know this.

1) Insert a column somewhere. (Probably on the far end of your data)

2) Number it from 1 to the number of rows you want to look at. The purpose of this is to return to the original ordering, because part of my suggestion involves sorting.

3) Sort the data by Column C, D, G. What does this do? It makes it so all rows that pertain to the same values in C, D, G are contiguous. The benefit? It means we don't need to look at all 65,000 rows to find the data.

4) Using code similar to the following, go through each row to determine whether it is Out of Tolerance.

Public Sub CalculateIt(i As Long, WS As Worksheet)
  Dim j As Long
  Dim t As Long

  t = WS.Cells(i, 9) 'Column I

  j = i + 1
  While (WS.Cells(i, 3) = WS.Cells(j, 3) _
         And WS.Cells(i, 4) = WS.Cells(j, 4) _
         And WS.Cells(i, 7) = WS.Cells(j, 7))
    t = t + WS.Cells(j, 9)
    j = j + 1
  Wend
        
  If Abs(t) >= 1000 Then
    WS.Cells(i, 11) = "Out of Tolerance"
  Else
    WS.Cells(i, 11) = ""
  End If

End Sub

Public Sub Testing()
  Dim WS As Worksheet
  Dim MyLastRowA As Long
  Dim i As Long
  
  Set WS = Worksheets("Sheet1")  'Substitute the name of the Worksheet
  MyLastRowA = WS.Range("A65536").End(xlUp).Row

  For i = 1 To MyLastRowA
    If i = 1 Then
      Call CalculateIt(i, WS)
    Else
      If (WS.Cells(i, 3) = WS.Cells(i - 1, 3) _
          And WS.Cells(i, 4) = WS.Cells(i - 1, 4) _
          And WS.Cells(i, 7) = WS.Cells(i - 1, 7)) Then
        WS.Cells(i, 11) = WS.Cells(i - 1, 11) 'Column K values stay the same
      Else
        Call CalculateIt(i, WS)
      End If
    End If
  Next i
End Sub

5) Re-sort your data by the column that was inserted with the line numbers.

6) Delete the extra column.

If you don't care about the order of the data, skip steps 1, 2, 5, and 6.

This won't be ultra-fast... but it will be significantly faster than what you had. If you want it to be faster, you'll have to pull the 4 columns into memory initially. The do your calculations and output the results to another array. Then dump that array back into the worksheet. However, this method above is probably simpler to code. If you find the speed to be still too slow, say so.

Scott

Hi Scott,

Good Advice.

I can't get past this... when i run the macro it errors and says that an object is required for the following... I'm still new to VBA... Your help is much appreciated.

t = WS.Cells(i, 9) 'Column I

Unfortunately, I probably need to see all your code to help.

The only possibility that I can think of off the top of my head is that WS isn't declared, but it should be declared in the parameters to the subroutine.

ie. Public Sub CalculateIt(i As Long, WS As Worksheet)

Scott