Line only calculated once when their are multiply lines for that invoice

[Whizbit412]

I have a spreadsheet with a Pivot Table in, we cannot get it to calculate how we would like can anyone help? I have uploaded an example of the data, what the pivot table is doing and what is required. The function of this excel sheet will be to copy data into on a monthly data and refresh the pivot for the most up to date figures

Appreciate any help

[Glenn Kennedy]

Take a look here. I don't think it's what you want, becasue I don't understand what you want!!

[Whizbit412]

Here is a new example of how the data is extracted from our system. You can see there are multiple lines for some invoices, when I try to calculate the totals in the pivot table it is adding every line of the invoice to the total when in fact it needs to only include 1 line per invoice. However I do still need to be able to see the ticket types.
Example - if someone has booked 4 spaces excel displays 4 lines for that invoice if the person has paid it totals 4 x the ticket price instead of seeing it as 1 invoice with 1 outstanding balance. hop that makes sense

[Glenn Kennedy]

??? There is no difference between "Pivot Table" and "Required"....

[Glenn Kennedy]

So, maybe this?? See Sheet1. Still rather confused, though!!

[Whizbit412]

Sorry i am probably not being very clear. On the attached sheet I have highlighted a line on the required tab. That invoice only has the balance outstanding once whereas the Pivot table is counting it 4 times. the balance outstanding for invoice 200000099 is 230.40 with a net of 268.80 and VAT 38.4.

[JeteMc]

I don't believe that a pivot table will do what you want, however if you change the formula in column C of sheet 1 (file attached to post #6) so that it reads as follows I feel that it gives you the results you are looking for:

Please Login or Register  to view this content.

Let us know if you have any questions.