Total amount checked from all records

[Tiaan_Terblanch]

Good Day,

We added a CSV file with all our invoice amounts, and we have also added a CSV with all our credit notes.
Is there a formula that will automatically deduct the credit note amount from the invoice amount based on the Document Type column?

So I need a formula that will automatically deduct the credit note amount from the invoice amount based on the document type that they are.

Screenshots are attached to show what it looks like when we import it. 

[Rupert_Hoffsch1]

Hi @Tiaan_Terblanch, you can use an if statement in a formula. So IF({Document Type} = "Credit", "INVOICE AMOUNT" - "CREDIT AMOUNT")

Sorry can't see your exact fields.

Hope that helps!

[Tiaan_Terblanch]

Hi Rupert, thank you so much for your response. I tried the formula, but it does not work, so I am adding more information.

You will see from the Original Amount column until the Tax Amount column are all formula columns, as I had to add this formula: 

{Original Amount [Original]}/100 as Airtable does not provide decimals with a comma.
Okay, now I want to deduct the credit note amount from the invoice amount in all these columns (it can happen separately) so that the total amount at the bottom reflects the deduction.

What would that formula be, and how do I add it at the back of my original formula so it all works in that column?

I hope you can help. Thanks

[Tiaan_Terblanch]

We need the credit amounts to be deducted from the invoice amounts and shown in the sum fields if we group them together.
The screenshot shows that the credit amount is added to the invoice amount to give the group the total amount.
But in reality, the credit amount should be deducted from the invoice amount, and the correct amount should show in the sum amount for the group.

I hope I make sense.

Thanks