Returning the Max Index and Sum in Lookup

[jamesbeale]

Hi all,
I have a 2 column table which contains customer names and order values. As customers place more orders, they are added to the table, therefore I have multiple entries for each customer.
Can someone advise on how I can retrieve the customer name and cumulative total for that has the customer that has the highest cumulative value of all orders?

Example below, what I want to return is "Customer A - 900.00"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer A[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Customer B[/TD]
[TD]200.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Customer C[/TD]
[TD]300.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Customer A[/TD]
[TD]200.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Customer C[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Customer A[/TD]
[TD]400.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Customer B[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Customer A[/TD]
[TD]200.00[/TD]
[/TR]
</tbody>[/TABLE]

Thanks.

 

[Special-K99]

Perhaps Pivot Table?
Do a Google and have a look.

 

[jamesbeale]

@Special-K99 I want to pull only the highest item through onto another summary worksheet, not the entire table.

 

[Special-K99]

Ok, got ya.

Here's one way.

in C1
=SUMIF(A$1:A$8,A1,B$1:B$8)
and copy down

in D1
=INDEX(A1:A8,MATCH(MAX(C$1:C$8),C$1:C$8,0),1)

in E1
MAX(C1:C8)

D1 and E1 contain Customer name and highest cumulative total.

I can't quickly see a way of doing this in one go.

Something you should consider too:
What happens if there's more than one customer with the highest cumulative total?
This solution will only list the first one.