Need sum of top values in a range based on criteria

[CSchwass]

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

 

[Mike H]

Hi,

The question is too ambiguous to attempt an answer. Lets take 1 piece of
sample data

A12,512

Is this all in 1 cell?

how is the 'number' split up,
is it A and the number 12512?
Is it A12 and the number 512?

Mike

 

[Lars-Åke Aspelin]

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

 

[Pete_UK]

Put the categories A, B, C, D and E in C1:C5, then put this array*
formula in D1:

=SUMPRODUCT((LEFT(A$1:A$120,1)=C1)*(B$1:B$120>=LARGE
(IF(LEFT(A$1:A$120,1)=C1,B$1:B$120),10)),B$1:B$120)

(All one formula - I've manually broken it after LARGE to avoid
awkward line-breaks on the newsgroups). I've assumed your data is in
rows 1 to 120, so change these if necessary.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula you need to use CSE again.

If you really need to have the phrase "Category A" in C1, then change
the two references to C1 to RIGHT(C1) in the formula (one character is
assumed by RIGHT if not specified).

Then copy the formula down to D5.

One drawback with using LARGE is that you might have duplicate values
that just happen to be 10th and 11th (or more) in magnitude - this
formula will sum all values that are greater or equal to the 10th
value, which in this example would include the 11th.

Hope this helps.

Pete

 

[CSchwass]

CSV. A12 would be cell B1 and 512 would be cell B2. What is the standard
format to post a range here?

 

[CSchwass]

Lars-Ã…ke,

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

Thank you!
Chris