Detect Duplicate and Missing Payments with COUNTIFS and Aging Buckets

If full reconciliation (Reconcile Payments vs. Invoices) is overkill for your scale — or you just want a quick monthly sanity check — this shorter workflow surfaces the two most costly payment problems in under 15 minutes: duplicate payments (you paid or received money twice) and aging-missing payments (invoices the counterparty still owes you).

Step 1: Duplicate Detection via COUNTIFS

On your Payments Received sheet, add a helper column next to Invoice #:

=COUNTIFS(F:F, F2, D:D, D2)

Where F is Invoice # and D is Amount. This counts how many rows share both the same Invoice # and the same Amount — a duplicate payment appears as 2 or higher.

Conditional formatting: anything > 1 gets a red fill. Sort descending by the helper column; the dupes float to the top.

Why match on both Invoice # and Amount, not just Invoice #? Because legitimate partial payments share Invoice # but have different Amounts. Matching on both catches the exact-copy dupes while letting partial payments through.

Step 2: Aging Buckets on Unpaid Invoices

On your Invoices sheet, add an Aging Days column:

=IF(Status="PAID", "", TODAY()-Due_Date)

Then categorize:

=IF(Status="PAID", "Paid",
  IF(Aging<=30, "Current",
  IF(Aging<=60, "30-60 days",
  IF(Aging<=90, "60-90 days", "90+ days"))))

Apply conditional formatting to the bucket column:

• Paid = grey
• Current = green
• 30-60 = yellow
• 60-90 = orange
• 90+ = red

Step 3: Monthly Summary PivotTable

Select both columns → Insert > Pivot table. Put Bucket in Rows, Amount in Values (SUM). Your output:

Bucket	Total Owed
Current	$42,000
30-60 days	$18,000
60-90 days	$9,000
90+ days	$12,500

The 90+ bucket is where the write-down lives. Monthly, verify it’s not growing.

Step 4: Surface Repeat Offenders

Which licensees are consistently late? Pivot by Licensee + Bucket:

• Any licensee where > 50% of their invoices land in 60-90 or 90+ needs a payment-terms review.
• A licensee with one stale 90+ invoice might just have one orphan; no pattern, no action.

Common Issues

• Statement dates vs. due dates. If your Due Date column actually holds the Statement Date (issued, not due), aging numbers are off. Always derive Due Date = Statement Date + payment terms (typically net-30 or net-60).
• Credits muddle the picture. If you issue a credit note, create an offsetting row with negative Amount, don’t delete the original invoice. The pivot math still works; the audit trail is preserved.
• Timezone drift. TODAY() in Sheets is workspace-local. If your team spans timezones, use a named cell with a fixed anchor date instead, and update it monthly.

Download the Template

The Payment Reconciliation template has both the dupe-detection helper column and the aging-bucket formulas pre-wired. Use its quick-scan view or run the full reconciliation depending on what you need.