Summing the n largest fees based on diff column

[Daniel Bonallack]

This is a simplified version of the problem I have in my
spreadsheet.

I have sales people's names in cells A1:A100. I have
their age in column B. In Column C I have their annual
sales.

I want to return one value with a worksheet function, and
that is:
What are the combined sales of the 10 oldest sales reps?

Thanks in advance for any help.

regards
Daniel

 

[Tom Ogilvy]

=SUMIF(B:B,">="&LARGE(B:B,10),C:C)

This would fall give more than 10 if the 10th and 11th had the same
birthday.

 

[Don Guillett]

One simple way may be to sort (descending) by the ages and just sum the top
10 in the adjacent column.

 

[Biff]

I like Don's solution but this array entered formula will
account for people that are the same age:

=SUM(IF(B2:B100>=LARGE(B2:B9,1),C2:C100,IF(B2:B100<=LARGE
(B2:B100,10),C2:C100,0)))

Biff