Summing up Unique Records
 

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

Summing up Unique Records
 

Apply an advanced filter on Diagram A as follows:

DataFilterAdvanced 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:

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

Summing up Unique Records
 

U¿ytkownik "Isabelle" napisa³ w
wiadomo¶ci ...
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


You can use also pivot table to achieve this result:
Mark data to sum in Diagram A and choose
Data-PivotTable and PivotChart Report
and then in wizard drag "Name" and "Phone Num" into
"Drop Row Fields Here" area and then drag "Revenue"
into "Drop Data Items Here" area.
--
--
==============================
Janusz Pawlinka