Function Problems

Excel: Copy an Invoice Amount When a Paid Date Is Entered

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 868 views

Question details

Display an invoice amount in another cell when a paid date is entered, and use paid amounts to calculate the remaining balance.

Copy an Invoice Amount When a Paid Date Is Entered in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
An invoice worksheet has billed amounts in column B, paid dates in column D, and a column for paid amounts in column O.
Observed behavior
The paid amount should remain blank until the corresponding paid date is entered, then display the billed amount.
Before you start

Identify the columns for the billed amount, paid date, and paid amount. The example uses B2, D2, and O2; adjust the cell references to match your worksheet.

Solution 1Recommended

Use an IF formula to display the paid amount

Use this formula when entering a paid date means the entire billed amount has been paid.

1
Enter the formula

Select O2 and enter =IF(D2="","",B2), then press Enter.

2
Test the result

Leave D2 empty to confirm O2 appears blank. Enter a paid date in D2 to display the amount from B2 in O2.

3
Apply it to other invoices

Select O2 and drag its fill handle down column O to copy the formula to the other invoice rows.

4
Calculate the balance

In an empty cell, enter =SUM(B2:B10)-SUM(O2:O10) to subtract the paid amounts from the billed amounts for rows 2 through 10. Adjust both ranges to include your invoice rows.

Use an IF formula to display the paid amount
How the date check works: The formula checks whether D2 is empty; it does not verify that the entry is a valid date. It also treats the full amount in B2 as paid, so it is not suitable for recording partial payments without changes.
Work with invoice formulas in WPS

Track Paid Invoice Amounts in WPS Spreadsheets

Use the same IF formula in WPS Spreadsheets to show an invoice amount when a paid date is entered and calculate the outstanding balance.

  1. 1. Open the invoice worksheet: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Add the paid amount formula: Select O2, enter =IF(D2="","",B2), and press Enter. Change the references if your columns differ.
  3. 3. Fill the invoice rows: Drag the fill handle from O2 down column O, then enter paid dates in column D to display the corresponding amounts.
  4. 4. Calculate the balance: Select an empty cell and enter =SUM(B2:B10)-SUM(O2:O10), adjusting the ranges to match your invoice rows.
Display paid amounts automatically when paid dates are enteredCalculate outstanding balances with SUM formulasOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the paid amount appear when D2 contains text?

The formula =IF(D2="","",B2) checks only whether D2 is empty. Any nonempty entry, including text, causes it to display B2.

How do I apply the formula to every invoice?

Enter the formula in O2, then drag the fill handle down column O. The relative cell references update for each row.

Can this formula track partial payments?

Not as written. It displays the full billed amount once D2 is nonempty. For partial payments, enter the actual paid amount in a separate column and subtract it from the billed amount.

How do I calculate the balance for a range of invoices?

In an empty cell, enter =SUM(B2:B10)-SUM(O2:O10). Change both ranges to cover the same invoice rows in your worksheet.