How to allocate payment receipts against invoices in Google Sheets?

Using Vlookup in Google Sheets we can allocate matching single receipts of payments effortlessly.

Assume there is an invoice (No. RA1564) to the tune of $5,000.00 raised to customer A. When you receive a payment against this invoice, you can easily match it as you have the invoice number.

Allocating matching multiple payments are also not much complicated with a Vlookup and Query formula combination.

Against the said invoice, suppose the customer has issued two payments like $2,000.00 and $2,500.00. This time you can summarise the payments received and then match the invoice.

But when you want to allocate payment receipts against invoices in Google Sheets without invoice number or any other reference to match other than customer name, things are not that much easy.

Assume you have raised two invoices to customer A and they are RA-1564 amounting to $5000.00 and RA-1565 amounting to $11,000.00.

You got a one-time payment to the tune of $8,000.00 against these invoices.

This time there is no invoice number to match. But you need to allocate the payments received like;

In this Google Sheets tutorial, we can learn how to properly allocate (distribute) payment receipts against invoices. I’ll explain all the above scenarios with examples below.

Invoice Details (Sample Data) for Allocation

Copy-paste this accounts statement (statement of account) in a new Google Sheets file (range is Sheet1!A1:F12).

Table # 1:

Before allocating payments in the ‘Credit’ column and finding the outstanding balance in the ‘Balance’ column, let’s try to match receipts.

Match Single Payment Receipts Using Vlookup in Google Sheets

Assume you have received the following payments.

(range is Sheet2!A1:B4)

Table # 2:

How to compile both the reports (Table # 1 and Table # 2)?

In Sheet1!E2, i.e. in the first row in the credit column, insert this formula.

=ArrayFormula(IFNA(vlookup(C2:C,Sheet2!A2:B,2,0)))

The Vlookup formula above lookup Table # 2 for the search_keys (invoice numbers) from Table # 1 for a match.

If found a match, it would return the value (receipt amount) from the second column from Table # 2.

You can keep on adding new receipts under Table # 2. The Vlookup will automatically match it and will insert the amount in Table # 1. Because I have included future rows (open/infinite range) in the formula.

Before starting how to allocate payment receipts against invoices, let’s address one more issue that the above matching may bring.

It’s about on account aka partial payments. I mean multiple payment receipts against the same invoice as well as a single payment.

Match Multiple Payment Receipts Using Vlookup and Query Combination in Google Sheets

For the explanation purpose, I am modifying the above Table # 2.

(range is Sheet2!A1:B5)

Table # 2 (Copy):

Here the earlier Vlookup won’t correctly allocate (better to say ‘match’) the payments received!

You first need to summarise the receipt, I mean Table # 2 (Copy) above, and use that as the ‘range’ (lookup table) in Vlookup.

The following Query formula generates the summary of Table # 2 (copy). The output will be similar to Table # 2 with no repeating invoices.

=query(Sheet2!A1:B,"Select A, Sum(B) Where A is not null Group by A",1)

Simply replace Sheet2!A2:B with this Query in Vlookup.

Formula to use in Sheet1!E2:

=ArrayFormula(IFNA(vlookup(C2:C,query(Sheet2!A1:B,"Select A, Sum(B) Where A is not null Group by A",1),2,0)))

Allocation of Payment Receipts Against Invoices in Google Sheets

Assume you have the mix of the above two examples (receipts) and also lump-sum receipts. Then how to get the customer account balance correctly by allocating such payment receipts.

Let us go to the detail of the problem first.

We have received payments from different customers as below.

Table # 3:

(Range Sheet2!A1:B6)

We don’t have any invoice number to allocate these receipts amounts. Also, the amount may or may not match as some of the receipt amounts are lump-sum and some are part payments.

Since we have the customer names as common in Table # 1 and Table # 3, we can use that info to allocate the full/partial/on account payment receipts against invoices.

The payment allocation formula for Sheet1!E2 which must be copy-pasted down (not an array formula as compared to the earlier two examples).

=min(D2,sumif(Sheet2!$A$2:$A,$A$2:$A,Sheet2!$B$2:$B)-sumif($A$1:A1,A2,$E$1:E1))

How this Google Sheets Formula Allocates Payment Receipts Correctly?

Formula Explanation

There are two Sumif formulas in use and one Min formula.

Sumif Formula # 1:

=sumif(Sheet2!$A$2:$A,$A$2:$A,Sheet2!$B$2:$B)

Try this first Sumif formula in cell G2 and copy-paste down.

It sums the payment receipts in customer wise and then returns against each customer in column G.

You must try the formula, then only you can understand.

Note: With minor tweaking, the earlier Vlookup and Query combination can do the same (can replace the Sumif formula # 1) and perform even better as an array formula. But we need the above Sumif one.

For your reference here is that combo. You can ignore it.

(just key in cell J2 to auto-populate)

=ArrayFormula(IFNA(Vlookup(A2:A,query(Sheet2!A1:B,"Select A,Sum(B) where A is not null group by A",1),2,0)))

What more! The just above Sumif as an array can do the same. Again ignore it.

(just key in cell J2 to auto-populate)

=ArrayFormula(sumif(Sheet2!$A$2:$A,$A$2:$A,Sheet2!$B$2:$B))

Simply ignore the just above Sumif Array and Query Combo formulas applied in J2. We don’t want both of them to allocate payments.

Sumif Formula # 2:

=sumif($A$1:A1,A2,$E$1:E1)

This Sumif is the key to allocating payment receipts against invoices. We can’t test it as above. But I can explain what it does.

It returns the cumulative sum (running total) of the values in the same column (column E) up to the current row like a total of $E$1:E1 in E2, a total of $E$1:E2 in E3 and so on.

The returned running total will be customer wise as we have used Sumif for the same.

We can combine both the above Sumif formulas with Min. See that below.

Generic Formula to Allocate Payment Receipts in Google Sheets

The final formula (generic version) in cell E2 is like =min(D2, Sumif Formula # 1 - Sumif Formula # 2 ). What does it do?

This will explain.

In E2: min(3578.80,11500-0)
In E3: min(2967.92,11500-3578)
In E4: min(12742.84,11500-6546.72)

The Min makes it sure that you are not allocating an amount greater than the debit (invoice) amount in the case of on account receipts.

Also, the receipt (lump_sum receipt) value got adjusted in each row. In any row the allocation (credit) amount is less than the debit (invoice) amount, Min returns the allocation amount.

What About the Allocation of New Payments?

Assume, using my formula, you have already allocated the payments received. Next time when you receive payments inserts them just below the last non-blank row in Table # 3.

For example, I have received an amount of 10,000.00 from the customer A. I can put them in the 7th row (insert customer name “A” in A7 and amount 10,000.00 in B7).

The payment will be automatically get allocated (adjusted) in the statement of account in Sheet1.

Google Sheets Formula for Outstanding Balance

Key this formula in cell F2 and forget about it!

=ArrayFormula(if(len(A2:A),D2:D-E2:E,))

That’s all about allocating payment receipts in Google Sheets