sum unique in pivot table

[morodan]

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

 

[Bernie Deitrick]

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