I am trying to sum col L on Data Sheet if Col A on Sales Reps matches Col K
on Data Sheet.. I have attempted it below but i failed. Any help?

why not just adapt SUMIF to the vba. See the vba help for using functions in
code

--
Don Guillett
SalesAid Software

"Busybee" wrote in message
...
I am trying to sum col L on Data Sheet if Col A on Sales Reps matches Col K
on Data Sheet.. I have attempted it below but i failed. Any help?

Code:

  With Range("L2:L5000")
   sumL = 0
 'assuming the row range is 2 to 5000
 For r = 2 To 5000
  If Cells(Worksheets("Sales Reps")(r, 1)).Value = Cells(Worksheets("Data
 Sheet")(r, 11)) Then
    sumL = sumL + Cells(Worksheets("Data Sheet")(r, 80)).Value
  End If
 Next

With Range("H2:H5000")
.Formula = "=SUMPRODUCT(" & _
"--('Sales Reps'!A2:A5000='Data Sheet'!K2:K5000)," & _
"('Data Sheet'!CB2:CB5000))"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Busybee" wrote in message
...
I am trying to sum col L on Data Sheet if Col A on Sales Reps matches Col
K
on Data Sheet.. I have attempted it below but i failed. Any help?

Code:

   With Range("L2:L5000")
    sumL = 0
 'assuming the row range is 2 to 5000
 For r = 2 To 5000
   If Cells(Worksheets("Sales Reps")(r, 1)).Value = Cells(Worksheets("Data
 Sheet")(r, 11)) Then
     sumL = sumL + Cells(Worksheets("Data Sheet")(r, 80)).Value
   End If
 Next