Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum unique in pivot table
I've got following table:
Invoice Contract Amount Client Assignement #N/D GIFE 0051/08 #N/D GIFE 0091/08 1008-08 2008.30.3009 555.00 BMG 1008-08 1008-08 2008.30.3009 555.00 BMG 1009-08 #N/D GIFE 104/08 116-08 2008.30.3009 606.40 BMG 116-08 #N/D GIFE 119/08 151-08 2008.30.3011 606.40 BMG 151-08 #N/D GIFE 164/08 In this table, Assignements are unique, but Invoices aren't. I'd like to summarize my data so that I get the sum of unique Invoices; as you can see in row four, Assignements 1008-08 and 1009-08 are both covered under Invoice 1008-08, for an Amount of 555.00; I've paid 555.00, not 1110.00; I entered it twice to have in any row the whole information. If I try to build a pivot table, I get for Invoice 1008-08 a sum of 1100.00, which is the standard behaviour: is there a way to sum only unique values in a pivot table? How could I solve this question otherwise? Please tell me if in your opinion this is only a problem in the layout of my table... e.g. I could leave blank any second, third, fourth "Amount" of the same "Invoice" (which is what I've done now, waiting for more elegant solutions). Ciao, Dan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum unique in pivot table
Use another column, with the formula
=COUNTIF($A$1:$A2,A2)=1 starting in row 2 of that column, then copy down. It will return TRUE for the first instance of the Invoice number (assumed to be in column A). Then add that field to your pivot table and use it to show TRUE only. What you really should do is set up a true database to track charges and payments: Invoice Amount Action 1008-08 550 Paid 1008-08 -550 Charged etc. The other items could go in other tables... HTH, Bernie MS Excel MVP "morodan" wrote in message ... I've got following table: Invoice Contract Amount Client Assignement #N/D GIFE 0051/08 #N/D GIFE 0091/08 1008-08 2008.30.3009 555.00 BMG 1008-08 1008-08 2008.30.3009 555.00 BMG 1009-08 #N/D GIFE 104/08 116-08 2008.30.3009 606.40 BMG 116-08 #N/D GIFE 119/08 151-08 2008.30.3011 606.40 BMG 151-08 #N/D GIFE 164/08 In this table, Assignements are unique, but Invoices aren't. I'd like to summarize my data so that I get the sum of unique Invoices; as you can see in row four, Assignements 1008-08 and 1009-08 are both covered under Invoice 1008-08, for an Amount of 555.00; I've paid 555.00, not 1110.00; I entered it twice to have in any row the whole information. If I try to build a pivot table, I get for Invoice 1008-08 a sum of 1100.00, which is the standard behaviour: is there a way to sum only unique values in a pivot table? How could I solve this question otherwise? Please tell me if in your opinion this is only a problem in the layout of my table... e.g. I could leave blank any second, third, fourth "Amount" of the same "Invoice" (which is what I've done now, waiting for more elegant solutions). Ciao, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique count in Pivot Table | Excel Worksheet Functions | |||
pivot table-get unique count | Excel Discussion (Misc queries) | |||
Pivot table: how to count unique? need help! | Excel Discussion (Misc queries) | |||
unique records in Pivot Table | Excel Discussion (Misc queries) | |||
Count Unique in Pivot Table | Excel Discussion (Misc queries) |