Summing up Unique Records

[Isabelle]

Hi
If i have a list of data as shown in the list below, how can i sum up the
revenue of each unique client and reflecting all the unique name & Phone num
as well, without reflecting any duplicates.

E.g. The original data that i have is in Diagram A, showing all the
duplicates. The result that i need is in Diagram B, the duplicates are
removed and it also sum up the different revenues for client 1.

Pls advice how can i achieve the result in Diagram B. I've tried using
sub-toal but it can only show either name or phone num, it's not able to show
both data together.

Diagram A
Name Phone Num Revenue
Client 1 1234567 $40
Client 1 1234567 $50
Client 2 4455996 $10
Client 3 8899445 $80

Diagram B
Name Phone Num Amount
Client 1 1234567 $90
Client 2 4455996 $10
Client 3 8899445 $80

 

[Stefi]

Apply an advanced filter on Diagram A as follows:

Data>Filter>Advanced filter
In Advanced filter dialog box
check Copy to another place
list range: $A:$B
filter range: $A:$B
copy to: $E$1:$F$1 (this can be done only within the same sheet)
check unique records only
press OK

Enter this formula in G1:
=SUMIF(A:A,E2,C:C)
and fill it down to the end of the list!

Regards,
Stefi


„Isabelle†ezt írta: