Function Problems

How to Compare Excel Sheets and Mark Invoices as Paid or Unpaid

Huda Qurayshi Sep 25, 2026 869 views

Question details

The user needs to create Excel formulas that compare invoice records against payment transactions across different sheets to determine the payment status of each invoice.

How to Compare Excel Sheets to Mark Invoices as Paid or Unpaid

Product: Spreadsheet
Device & OS: not provided
Scenario: Reconciling invoice records with a master list of payment transactions to accurately track accounts receivable and outstanding balances.
Observed behavior: Requires a structured formula-based method to automatically identify and label invoices as fully paid, partially paid, or unpaid based on matching unique identifiers.

Before you start

Ensure both your invoice sheet and payment transaction sheet contain a corresponding column with unique invoice numbers. It is highly recommended to remove any trailing spaces from these identifiers so the formulas can match them properly.

Solution 1Recommended

Use SUMIFS and IF Formulas to Track Payment Status

Calculate total payments for each invoice using the SUMIFS function, then use a nested IF formula to categorize the payment status automatically.

The SUMIFS function is perfect for adding up multiple payments made against a single invoice. Once the total paid amount is calculated, a nested IF statement compares the total paid against the original invoice amount to define the current status.

Identify Unique Keys

Ensure your 'Invoices' sheet and 'Payment Transactions' sheet both contain the unique invoice numbers. For example, assume Invoice Numbers are in Column A on the Invoices sheet and Column D on the Payment Transactions sheet.

Calculate Total Payments

In your Invoices sheet, add a 'Total Paid' column (e.g., Column E). Enter the formula =SUMIFS('Payment Transactions'!$C:$C,'Payment Transactions'!$D:$D,INVOICES!$A2) where Column C is the payment amount.

Determine Payment Status

Add a 'Status' column next to the Total Paid column. Enter the nested IF formula: =IF(D2="","",IF(D2=E2,"Fully PAID",IF(E2=0,"UNPAID","Partially PAID"))). Here, D2 represents the original invoice amount and E2 represents the calculated Total Paid.

Apply Formulas to All Rows

Select the cells containing the two formulas and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the calculations to the rest of your invoice list.

Use SUMIFS and IF Formulas to Track Payment Status

Formula Adjustments: Always ensure your column and sheet references match your actual workbook setup, especially if your data is located in different columns or tabs.

Reconcile Invoices with WPS Spreadsheet

Manage Invoices Easily in WPS Office

WPS Spreadsheet offers comprehensive formula support, including advanced functions like SUMIFS and nested IFs, allowing you to easily reconcile invoices and track payment transactions with precision.

1. Open Your Workbooks: Launch WPS Spreadsheet and open the file containing your invoice and payment data.
2. Insert the SUMIFS Formula: Select the cell where you want the total paid amount calculated and type your SUMIFS formula to reference the transaction sheet.
3. Input the IF Formula: In the adjacent cell, enter the nested IF formula to automatically display "Fully PAID", "Partially PAID", or "UNPAID".
4. Apply Conditional Formatting: Go to the Home tab and use Conditional Formatting to highlight "UNPAID" statuses in red for immediate visual tracking.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsAdvanced data validation and conditional formatting for easy invoice trackingFree to download and use with a lightweight installation packageIntuitive and familiar interface for seamless migration from Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS formula returning zero even though the invoice numbers match?

This usually happens because of mismatched data types, such as invoice numbers being stored as text in one sheet and as numbers in the other, or due to hidden trailing spaces. Use the TRIM or VALUE function to clean up your data columns so they match identically.

Can I highlight unpaid invoices automatically?

Yes, you can easily highlight unpaid invoices using Conditional Formatting. Select your status column, go to 'Home' > 'Conditional Formatting' > 'Highlight Cells Rules' > 'Equal To', type 'UNPAID', and select a red fill format.

What if an invoice is overpaid? How do I update the IF formula?

You can add an additional condition to your IF formula to account for overpayments. For example, modify it to: =IF(D2="","",IF(E2>D2,"OVERPAID",IF(D2=E2,"Fully PAID",IF(E2=0,"UNPAID","Partially PAID")))).

How do I handle multiple payment sheets for different months?

If you keep separate transaction sheets for each month, it is best to consolidate them into one master transaction table. Alternatively, you can string together multiple SUMIFS functions by adding them: =SUMIFS(January_Data) + SUMIFS(February_Data).

Excel & Data Problems