WPS Spreadsheets: Exclude a Team from Excel Summary Totals
Question details
The user needs to exclude patients assigned to a specific team from an overall summary while showing how many records were excluded.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains patient or record assignments, categories, and values that must be summarized without including one selected team.
- Observed behavior
- The summary must count or total eligible records only and separately display the number of records removed because they belong to the excluded team.
Confirm which column contains the team assignment and which column contains the category, patient ID, or numeric value. Replace the example ranges and excluded team name in the formulas with the actual columns in your worksheet.
Use COUNTIFS or SUMIFS to exclude a team from the summary
Use this method when the source data is arranged in columns and you need a reliable total plus a separate count of excluded records.
Assume column A contains patient IDs, column B contains team assignments, column C contains categories, and column D contains amounts. Replace the example ranges with your own worksheet ranges.
Use a not-equal criterion such as "<>Team A" to exclude the selected team. Keep the excluded team name in a cell, such as F1, when you want to change the filter without editing the formula.
In a cell such as F1, type the team to remove from the summary, for example Team A.
Select the summary result cell and choose the formula bar or Formulas tab, then enter =COUNTIFS($B$2:$B$100,"<>"&$F$1,$C$2:$C$100,"Target Category") to count records in the target category that are not assigned to the excluded team.
For a numeric total, select another result cell and enter =SUMIFS($D$2:$D$100,$B$2:$B$100,"<>"&$F$1,$C$2:$C$100,"Target Category") to sum only values outside the excluded team.
In a separate result cell, enter =COUNTIFS($B$2:$B$100,$F$1,$C$2:$C$100,"Target Category") to show how many records from the target category were deducted.
Filter eligible records with FILTER before calculating the total
Use this option in WPS Spreadsheets versions that support FILTER when you want to display the qualifying records before counting or totaling them.
Build Exclusion-Based Summaries in WPS Spreadsheets
WPS Spreadsheets lets you create COUNTIFS, SUMIFS, and FILTER formulas to remove a selected team from a summary, calculate the remaining records or values, and display the deducted count in separate cells.
- 1. Open the workbook: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the team assignments and summary data.
- 2. Identify the data columns: Review the worksheet and note the columns for team, category, patient or record ID, and numeric amount.
- 3. Add the exclusion criterion: Click a summary cell and enter a COUNTIFS or SUMIFS formula using "<>"&F1, where F1 contains the team that should be excluded.
- 4. Calculate the deducted count: Click a separate result cell and use COUNTIFS with the team cell as the criterion to show how many matching records were removed.
Frequently Asked Questions
How do I exclude a team directly in a WPS Spreadsheets formula?
Use a not-equal criterion in COUNTIFS or SUMIFS, such as =COUNTIFS(B2:B100,"<>Team A",C2:C100,"Target Category"). This counts only target-category records not assigned to Team A.
How can I show how many records were excluded?
Use a second COUNTIFS formula with the excluded team as the criterion: =COUNTIFS(B2:B100,F1,C2:C100,"Target Category"). If F1 contains Team A, the formula returns the number of target-category records assigned to that team.
Can I exclude a team and sum amounts instead of counting patients?
Yes. Use SUMIFS with the same exclusion criterion, for example =SUMIFS(D2:D100,B2:B100,"<>"&F1,C2:C100,"Target Category"), where column D contains the amounts.
Why does my exclusion formula still include some records?
Check for extra spaces or inconsistent team names in the source column. Use the exact team label, clean the source data, and verify that the team and criteria ranges have the same number of rows.
What can I use if FILTER is not available?
Use COUNTIFS for record counts and SUMIFS for numeric totals. These functions provide the same exclusion logic and work well for fixed worksheet ranges.
