Oops. I didn't see the continuation of the thread.

(You guys already discussed it.)

Bob Phillips wrote:

If you must use VBA this works

Dim cell As Range
Dim rng As Range
Dim rng1 As Range
Set rng = Sheets("Sheet1").Range("L2:L1981")
Set rng1 = Sheets("Sheet1").Range("N2:N1981")
With Sheets("Sheet2")
For Each cell In Range("B2:B51")
cell.Value = Evaluate("SumProduct((" & rng.Address & "=""" & _
cell.Offset(0, -1).Value & """)*(" & rng1.Address & "
0))")
Next cell
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Sheet1 contains approx 2,000 rows of data, I want to summarise this sheet
on
Sheet2.

So far I have this:

Sub Macro1()
Dim cell As Range
Set rng = Sheets("Sheet1").Range("L2:L1981")
Set rng1 = Sheets("Sheet1").Range("N2:N1981")
With Sheets("Sheet2")
For Each cell In Range("B2:B51")
cell.Value = Application.SumProduct((rng = cell.Offset(0, -1)) *
(rng1 0))
Next cell
End With
End Sub

Sheet1 - column L contains names and column N contains dates.
Sheet2 - column A2:A50 are names from column L on Sheet1.

I get a Type Mismatch error and haven't been able to fix it. Thanks in
advance for any help/suggestions.

Gareth




--

Dave Peterson