Lars-Ã…ke,

This formula is simple, versatile, and seems to return exactly what I am
looking for. Brilliant.

Thank you!
Chris

"Lars-Ã…ke Aspelin" wrote:

On Fri, 28 Aug 2009 12:02:01 -0700, CSchwass
wrote:

I need to sum the top ten values for 5 categories in a data range.

I can sort/filter by the category and use SUM and LARGE, but I want to show
the results in 5 cells (one per category) and automate the process. I am
willing to create a macro if a formula can't cover it.

My data looks like this:

Category,Value
A12,512
B35,458
A42,430
C20,429
E12,409
E29,395
D30,350
etc.

...in Columns A and B. How can I get results like this:

Category A,{Sum of top ten values for category A}
Category B,{Sum of top ten values for category B}
Category C,{Sum of top ten values for category C}
Category D,{Sum of top ten values for category D}
Category E,{Sum of top ten values for category E}

Thanks,
CSchwass


Try this formula in the cell where you want the sum of the top ten
values for category A:

=SUM(LARGE(IF(LEFT(A1:A1000)="A",B1:B1000),ROW(A1: A10)))

Note: This is an array formula. Confirm with CTRL+SHIFT+ENTER rather
than just ENTER.

Change the 1000 in both places to fit the size of your data in columns
A and B.

replace "A" with "B" to get the sum of the top ten values for category
B etc.

Hope this helps / Lars-Ã…ke