How to Sum Unique Project Estimates in WPS Office Spreadsheets Excluding Contractor Not Awarded
Question details
The user needs to calculate the total Estimate Value for each scope of work, counting each project only once and excluding rows where Status is Contractor Not Awarded.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet contains multiple rows per project across scopes and statuses, and the user needs a formula to total estimate values by scope without double-counting projects or including unawarded contractors.
- Observed behavior
- Existing aggregation can double-count projects or include Contractor Not Awarded rows, so the result must target unique scope-project combinations.
Open your worksheet and confirm the columns used for Project, Scope of Work, Estimate Value, and Status. Make sure the status is spelled exactly as in the formula, for example 'Contractor Not Awarded', and that your WPS Office version supports dynamic array functions such as FILTER, UNIQUE, and XMATCH.
Sum Unique Project Estimates with FILTER, UNIQUE, XMATCH, INDEX, and SUM
Use this formula when you want a single-cell total by scope that counts each project once and excludes Contractor Not Awarded rows.
The formula filters projects by the selected scope and by a status other than Contractor Not Awarded. It then keeps unique project IDs, combines them with the scope name, and finds the matching rows to return and sum the Estimate Value.
Click the cell where you want the total for a scope, such as the cell next to a Sheetmetal summary label.
Type or paste the following formula, assuming Project is in A5:A35, Scope is in C5:C35, Estimate Value is in D5:D35, and Status is in F5:F35: =IFERROR(SUM(INDEX(D5:D35,XMATCH("Sheetmetal"&UNIQUE(FILTER(A5:A35,(C5:C35="Sheetmetal")*(F5:F35<>"Contractor Not Awarded"))),C5:C35&A5:A35))),0)
Change the ranges A5:A35, C5:C35, D5:D35, and F5:F35 to match your data, and replace Sheetmetal with the scope you want to total.
Press Enter to calculate the total. Spot-check a few project rows to confirm that duplicate project estimates are counted once and Contractor Not Awarded rows are excluded.
Use WPS Office Spreadsheets to Sum Unique Project Estimates by Scope
WPS Office Spreadsheets can directly perform this analysis with dynamic array formulas, so you can total unique project estimates by scope and exclude Contractor Not Awarded rows without helper columns or manual filtering.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the .xlsx or .xls file that contains the Project, Scope, Estimate Value, and Status columns.
- 2. Enter the formula: In the result cell, enter the IFERROR/SUM/INDEX/XMATCH/UNIQUE/FILTER formula with your actual row ranges and scope name.
- 3. Edit the scope and ranges: Replace the Sheetmetal placeholder and A5:A35, C5:C35, D5:D35, F5:F35 references to match your worksheet layout.
- 4. Review totals: Press Enter and compare the total with a manual filter to confirm duplicates are removed and Contractor Not Awarded rows are not included.
Frequently Asked Questions
Why does the formula return 0?
The most common cause is that the scope name or status text does not exactly match the cell values. Check that the scope name is spelled the same, that 'Contractor Not Awarded' has the same spacing and capitalization, and that the column ranges are correct.
Can I total all scopes at once instead of one scope at a time?
The formula shown is designed for one selected scope. To total multiple scopes, run the formula once per scope cell or use a helper-column or pivot approach that groups by Scope and excludes Contractor Not Awarded rows.
Do I need dynamic arrays for FILTER, UNIQUE, and XMATCH in WPS Office?
These functions require a version of WPS Office Spreadsheets that supports dynamic array formulas. If your version does not support them, update WPS Office or use a helper-column method.
What if my Project ID is numeric?
The formula still works because it concatenates the scope with the project ID. Ensure both the lookup array C5:C35&A5:A35 and the lookup value use the same data types and there are no hidden spaces in the Project column.
