How to Match Multiple Customer Payments to One Bank Deposit in Excel
To match a grouped bank deposit, total the customer payments on each deposit slip, then compare the slip total and reference with the bank entry. If two slips have the same date and amount and the bank export has no reference, keep both as candidates. Use the bank’s deposit detail or a deposit confirmation to determine which one belongs to that entry.
In this practice workbook, two bank deposits show $1,240 on November 12. One includes a slip reference and ties to two customer payments. The other has a blank reference and could belong to either of two slips. A third bank entry shows $980 against a $920 slip, leaving $60 to explain.
Browse more Jolene.pro practice workbooks.
What to pull before you start
| Record | Fields to keep |
|---|---|
| Bank export | Bank transaction ID, bank posting date, deposit amount, and any deposit reference |
| Deposit slip or batch detail | Slip reference, receipt ID, amount on the slip, and deposit date |
| Customer payment export | Receipt ID, customer, payment amount, and date received |
If you use QuickBooks Online, its Bank Deposit workflow groups customer payments from Undeposited Funds into a single deposit record. The same grouping gives you a total to compare with the downloaded bank entry. If the bank export has a short description, open the transaction details or pull the deposit confirmation before clearing an ambiguous row. QuickBooks Online bank deposits · QuickBooks bank-feed matching
Put the payments into deposit groups
- Total the lines by slip reference. The example uses
SUMIFSto add the amounts inDeposit Slipcolumn C for the slip references in column A. ForDS-4101, the two lines add up to $775 + $465 = $1,240. - Check each payment by receipt ID. Look up
P-201andP-202on the Receipts tab. Both amounts agree with the slip. Compare deposit date with bank posting date using the timing you see in your own records. Keep the customer payment date as a separate field. - Use the slip reference before looking at candidates. If a reference appears on more than one bank row, review both rows before clearing either. If the reference is missing, total each unused slip and look for candidates around the bank posting date. A date and amount can narrow the list. Use a confirmation or another shared reference to clear the row.
In Deposit Review!E6, this formula totals the slip when the bank row supplies a reference and the slip exists. It leaves the result blank when either piece is missing. Microsoft SUMIFS reference
=IF(OR(C6="",COUNTIFS('Deposit Slip'!$A$2:$A$7,C6)=0),"",SUMIFS('Deposit Slip'!$C$2:$C$7,'Deposit Slip'!$A$2:$A$7,C6))
The ranges cover the six practice lines. Use an Excel Table or expand the ranges when applying the formula to a longer export. For rows with a slip total, subtract it from the bank amount to show the difference.
Check all three deposits
| Bank entry | What the records show | Source rows |
|---|---|---|
| BK-101 | $1,240 ties. The bank shows DS-4101. Slip payments P-201 and P-202 add up to $1,240, and the amounts agree with the Receipts tab. | Bank Deposits!A2:D2, Deposit Slip!A2:D3, Receipts!A2:D3 |
| BK-102 | Leave $1,240 open. DS-4102 and DS-4103 each total $1,240 on November 12 and neither is assigned to another bank row. DS-4101 is already assigned to BK-101. Pull the bank confirmation for BK-102. | Bank Deposits!A3:D3, Deposit Slip!A4:D5, Receipts!A4:D5 |
| BK-103 | $60 unexplained. The bank shows $980 for DS-4104. Its two receipts total $600 + $320 = $920. Check the bank detail and source records for an omitted or miskeyed line. | Bank Deposits!A4:D4, Deposit Slip!A6:D7, Receipts!A6:D7 |
If you sort each list and pair the first occurrence of $1,240 with the first $1,240 slip, your spreadsheet can produce a tidy match while the underlying deposit is still uncertain. In this case, P-203 belongs to Northline Cafe and P-204 belongs to Marlowe Repair. Crediting the wrong receipt would leave the other customer’s payment unresolved.
Try the example in Jolene.pro
Open the supplied practice workbook. The dates, customer names, and transactions are synthetic. In Jolene.pro, upload the workbook and ask:
Use Bank Deposits, Deposit Slip, and Receipts to check each bank entry. Which deposit ties, which has more than one same-date, same-amount candidate, and which has an amount difference? Exclude slips already assigned to another bank entry when listing candidates. Show the receipt IDs, the calculation, and the exact worksheet ranges. Leave any uncertain match open.
Compare the answer with the three outcomes above. For another month-end check, see Vendor statement reconciliation in Excel.
Combining checking and credit card exports for a spending report? See how to check a card repayment that posts across month end before counting it as another purchase.
1 comment