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"

	A	B
1	Customer A	100.00
2	Customer B	200.00
3	Customer C	300.00
4	Customer A	200.00
5	Customer C	50.00
6	Customer A	400.00
7	Customer B	100.00
8	Customer A	200.00

<tbody>
</tbody>

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.