Summing the n largest fees based on diff column

  • Thread starter Thread starter Daniel Bonallack
  • Start date Start date
D

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
 
=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.
 
One simple way may be to sort (descending) by the ages and just sum the top
10 in the adjacent column.
 
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
 
Back
Top