Working with duplicates

[TwinExcel]

Good morning,

I'm hoping someone may be able to help. I have a list of invoices with amounts and applicable notes. Even though invoices and amounts are the same, there are multiple rows for each invoice because the notes are different. As a result, using the example below, when I pivot the data it shows the amount due on invoice ABC is $1,000, not $500. Is there a way to adjust the pivot somehow or a way to adjust the raw data. I don't simply want to remove duplicates bc I still want to see the note detail if drilling down to the detail in the pivot table?

Invoice Amount Note
ABC $500 1st Attempt
ABC $500 2nd Attempt

Thanks,
Jef

 

[BarryL]

Can you add the "Note" field to Report Filter? then use the filter to filter for 1st Attempt?

 

[Asala42]

I'd probably just add a helper column on your data. It's especially easy the data is already sorted by Invoice.

=if(A2<>A1,B2,0)

Then you can simply sum by the helper column.

 

[SpillerBD]

Instead of using SUM in the Pivot Table functions, why not MAX?