Assuming a sort 1st by employee number ascending and then amount
descending,use

=SUM(INDIRECT("c"&MATCH(a2,$A$1:$A$200,0)&":c"&MAT CH(a2,$A$1:A$200,0)+4))
assumes that the employee number you want is in a2

if another sheet employee number 12345 in cell a2. Correct to one line
=SUM(INDIRECT("sheet8!c"&MATCH(A2,Sheet8!$A$1:$A$2 00,0)&":c"&MATCH(A2,Sheet8
!$A$1:$A$200,0)+4))

BTW, you may use this formula to convert formulas that result in more than
one line to the line above

Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
Cells(x - 1, y) = Cells(x - 1, y) & C
'mstr = mstr & C
Next
'Cells(x - 1, y) = mstr
End Sub

--
Don Guillett
SalesAid Software

"Chris" wrote in message
om...
I have a workbook with two worksheets

Worksheet #1 has the following:

Employee# Year Amount
12345 2002 40000
12345 2001 26390
12345 2000 33184
12345 1999 38861
12345 1998 25198
12345 1997 32015
98765 2002 67923
<etc etc etc etc

Worksheet #2 has a cell that I need to put the average of the five
highest years of pay for each employee record.

Any ideas how to write this function?

Thanks in advance.

Chris