Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get unique records? | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Summing the # of records | New Users to Excel | |||
Unique records | Excel Discussion (Misc queries) | |||
unique records | Excel Worksheet Functions |