How to Sum Excel Values While Ignoring Incomplete or Error Rows in WPS Spreadsheets
Question details
User needs a reliable WPS Spreadsheets formula to sum completed profit and loss values while excluding rows that return #N/A due to incomplete data.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet contains profit and loss rows; incomplete rows display #N/A and should be ignored when calculating the total.
- Observed behavior
- A standard SUM or SUMIF formula returns an error or includes incomplete rows, so the total is not reliable.
Identify the exact range that contains the profit and loss values and confirm which rows return #N/A because required inputs are missing.
Use AGGREGATE to Sum the Range While Ignoring #N/A Errors
Best when the profit and loss range contains error values and you want one formula that sums only valid numbers.
AGGREGATE with function_num 9 performs SUM, and option 6 ignores error values such as #N/A. It works without array entry in WPS Spreadsheets.
Click the cell where you want the completed total to appear.
Type =AGGREGATE(9,6,profit_loss_range) and replace profit_loss_range with your actual range, such as C2:C100.
Press Enter. The formula returns the sum of all numeric completed values and skips #N/A cells.
Use SUMIFS to Include Only Completed Rows Based on a Status Column
Use this method when the worksheet has a separate status or completion column that marks completed rows.
Change Source Formulas to Return Blank or Zero When Inputs Are Missing
Use this approach when you can edit the underlying formulas and want to prevent #N/A errors at the source.
Sum Values While Ignoring Error Rows in WPS Spreadsheets
Use the AGGREGATE formula in WPS Spreadsheets to total completed profit and loss values while automatically skipping #N/A error rows, without complicated array formulas.
- 1. Open the workbook in WPS Spreadsheets: Double-click the Excel-compatible workbook file or open WPS Office and browse to the file.
- 2. Select the result cell: Click an empty cell where you want the completed profit and loss total to appear.
- 3. Enter the AGGREGATE formula: Type =AGGREGATE(9,6,C2:C100) and replace C2:C100 with your actual profit and loss range.
- 4. Press Enter and confirm: Press Enter and verify that only completed numeric values are summed while #N/A error rows are ignored.
Frequently Asked Questions
Can AGGREGATE ignore errors other than #N/A?
Yes. In WPS Spreadsheets, =AGGREGATE(9,6,range) uses option 6 to ignore all evaluated error values in the range, including #DIV/0!, #VALUE!, and #REF!.
Does AGGREGATE work when the file is later opened in Microsoft Excel?
Yes. AGGREGATE is supported in Excel 2010 or later and in WPS Spreadsheets, so the workbook remains formula-compatible when you switch between applications.
How do I make incomplete formulas return blank instead of #N/A before summing?
Wrap the source formula with IFERROR, such as =IFERROR(existing_formula,0), or check missing inputs with IF so the cell returns blank or zero, then use a normal =SUM(range).
Why is SUMIFS better when I have a status column?
SUMIFS can include only rows whose status column equals Completed, so incomplete rows are excluded by the condition before their error values affect the total.
