Excel: Copy an Invoice Amount When a Paid Date Is Entered
Question details
Display an invoice amount in another cell when a paid date is entered, and use paid amounts to calculate the remaining balance.

- 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.
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.
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.
Select O2 and enter =IF(D2="","",B2), then press Enter.
Leave D2 empty to confirm O2 appears blank. Enter a paid date in D2 to display the amount from B2 in O2.
Select O2 and drag its fill handle down column O to copy the formula to the other invoice rows.
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.

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. Open the invoice worksheet: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Add the paid amount formula: Select O2, enter =IF(D2="","",B2), and press Enter. Change the references if your columns differ.
- 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. Calculate the balance: Select an empty cell and enter =SUM(B2:B10)-SUM(O2:O10), adjusting the ranges to match your invoice rows.

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.




