How to Exclude Multiple Names in Another Range with SUMIFS in WPS Spreadsheets
Question details
The user needs to sum values in WPS Spreadsheets while excluding names that appear in a separate range, because SUMIFS cannot treat that range as a single not-equal condition.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a conditional sum formula that must exclude every name listed in a separate lookup range.
- Observed behavior
- SUMIFS only accepts one value at a time for a not-equal criterion and does not directly exclude an entire list.
Confirm that all ranges used in the formula refer to the same number of rows and are on the same sheet. Identify the sum range, the condition ranges, the criteria cells, and the exclusion list range before editing the formula.
Use SUMPRODUCT with MATCH and ISNA to Exclude All Names in a List
Use this when the excluded values are stored in a range and you need one dynamic formula instead of adding a separate SUMIFS criterion for each name.
The formula multiplies the condition results by the sum range and uses MATCH inside ISNA to keep only rows whose name does not appear in the exclusion list. Because SUMPRODUCT processes arrays natively, it can handle multiple conditions and a list-based exclusion in one step.
Click the cell where you want the total to appear in your WPS Spreadsheets worksheet.
Type a formula such as =SUMPRODUCT(($C$3:$C$14=$E$18)*($F$3:$F$14=$F$18)*ISNA(MATCH($X$3:$X$14,$D$2:$D$10,0))*$E$3:$E$14) and adjust each range to match your data layout.
Press Enter to calculate the total. Rows with names found in the exclusion list are treated as FALSE and do not contribute to the result.
Use SUMIFS with Individual Not-Equal Criteria for a Small Fixed List
Use this only when you have a very small, fixed list of names and prefer a straightforward SUMIFS formula.
Create a Dynamic Exclusion List Formula in WPS Spreadsheets
WPS Spreadsheets can accomplish this with SUMPRODUCT and MATCH: use one formula to sum rows based on criteria while automatically excluding every name in a separate range.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains your data and exclusion list.
- 2. Select the result cell: Click the cell where the total should appear.
- 3. Enter the dynamic formula: Type the SUMPRODUCT formula with MATCH and ISNA, then adjust the sum range, criteria ranges, and exclusion list range to your sheet.
- 4. Apply and verify the result: Press Enter and spot-check a few included and excluded rows to confirm the formula is calculating correctly.
Frequently Asked Questions
Why does SUMIFS not accept a range as multiple not-equal criteria?
SUMIFS treats each criterion argument as a single condition. A reference like "<>"&D2:D10 is not processed as multiple OR exclusions in one argument, so you need SUMPRODUCT with MATCH or several individual criteria.
Can I exclude blanks at the same time as multiple names?
Yes. In SUMPRODUCT, add another condition such as *($X$3:$X$14<>"") and make sure all array ranges have the same number of rows.
Which formula is more maintainable: SUMIFS with individual criteria or SUMPRODUCT with MATCH?
SUMPRODUCT with MATCH is more maintainable because the exclusion list can grow or change without editing the formula criteria one by one.
What should I do if the formula returns #N/A?
Check that all array ranges have the same number of rows, the MATCH lookup range is correct, and the compared values do not have incompatible data types.
Does this formula work in both WPS Spreadsheets and Microsoft Excel?
Yes. SUMPRODUCT, MATCH, and ISNA are supported in both applications, so the same formula works when you open or save as a .xlsx file.
