How to Subtract Paid Bills from an Excel Total in WPS Office
Question details
The user needs to calculate the outstanding bill total by adding only amounts whose status is not marked PAID, rather than subtracting one paid row from a running total.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A bill register in WPS Office Spreadsheets contains amounts in column F and payment statuses such as PAID in column G; the user wants an accurate outstanding total for all unpaid rows.
- Observed behavior
- Subtracting paid rows one by one from a running total is error-prone and does not automatically recalculate as statuses change or new paid rows are added.
Confirm that the status column uses consistent text such as PAID and that the amount column contains numeric values. Use the same spelling and capitalization for all status labels so the formula criteria match correctly.
Use SUMIF to Sum Only Amounts Not Marked PAID
Use this formula when you want the outstanding total to include all rows whose status is anything other than PAID.
The SUMIF function adds the values in the amount column only when the corresponding status cell meets the condition. The criteria "<>PAID" means the status must not equal PAID, so unpaid, pending, and blank status rows are included.
Click the cell where the outstanding total should appear, such as F18, below the amount column.
Type =SUMIF(G3:G16,"<>PAID",F3:F16) in the formula bar and make sure the ranges match your actual data.
Press Enter to apply the formula and compare the result with a manual check of a few unpaid rows.
If your list extends beyond row 16, update both G3:G16 and F3:F16 to cover all data rows, such as G3:G100 and F3:F100.
Use SUMIFS for Additional Filtering Conditions
Use this variation when you need to exclude PAID rows and also apply another condition, such as due date or category.
Calculate Outstanding Bills in WPS Office Spreadsheets
WPS Office Spreadsheets lets you calculate outstanding totals with the same SUMIF and SUMIFS functions you know from Excel, making it easy to exclude PAID rows without manual subtraction.
- 1. Download WPS Office: Go to the official WPS Office website and download the free Windows, macOS, or mobile version for your device.
- 2. Open your bill workbook: Launch WPS Office, click Open, and select the Excel file that contains your bill list.
- 3. Enter the SUMIF formula: Click the total cell and type =SUMIF(G3:G16,"<>PAID",F3:F16) in the formula bar, then press Enter.
- 4. Save the workbook: Save the file in .xlsx or .xls format so the result remains compatible with Excel.
Frequently Asked Questions
What does the criterion "<>PAID" mean in the SUMIF formula?
It means “not equal to PAID.” The formula sums values in the amount range only when the corresponding status cell does not contain exactly PAID.
Why does my SUMIF formula return 0?
Check that the status column contains the exact text PAID without extra spaces and that the amount column contains numbers, not text. Also verify that your ranges cover the correct rows.
Can I exclude both PAID and CANCELLED rows from the outstanding total?
Yes. Use SUMIFS with two criteria, for example =SUMIFS(F3:F16,G3:G16,"<>PAID",G3:G16,"<>CANCELLED"). This sums amounts that are neither PAID nor CANCELLED.
Will the outstanding total update automatically when I mark a bill as PAID?
Yes. In WPS Office Spreadsheets, formulas recalculate by default. When you change a status to PAID, the row is excluded and the outstanding total decreases.
