Sum Incentives by Email and Exclude Parking in WPS Spreadsheets
Question details
Calculate each person's total incentive amount by email address while excluding records whose type is Parking, then find totals greater than $600.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs a separate summary table for unique email addresses and their qualifying incentive totals.
- Observed behavior
- Parking rows must not contribute to the total, and only email totals above $600 need to be identified.
Confirm that the email, incentive amount, and type columns cover the same data rows and that incentive values are stored as numbers. Use the exact category text "Parking" as it appears in the Type column.
Create an email summary with UNIQUE and SUMIFS
Use this method to generate one row per email address, calculate incentives excluding Parking, and filter qualifying totals.
This approach keeps the original transaction data unchanged and creates a clear summary table. Replace the sample ranges with your actual ranges or table column references.
On a blank area or a new worksheet, enter Email in one header cell and Total Incentives in the cell beside it.
In the first cell below the Email header, enter =UNIQUE(A2:A1000), replacing A2:A1000 with your email range. The formula spills one unique email address per row.
In the first cell below Total Incentives, enter =SUMIFS($B$2:$B$1000,$A$2:$A$1000,D2,$C$2:$C$1000,"<>Parking"), where column B is Incentive, column A is Email, column C is Type, and D2 is the first unique email. Fill the formula down beside the email list.
Select the summary table, choose Data > Filter, open the filter for Total Incentives, and set Number Filters > Greater Than to 600.
Use structured references for an Excel-style data table
Use this option when your source data is formatted as a table and you want formulas that expand automatically as new rows are added.
Use a PivotTable for a reviewable email total report
Use a PivotTable when you prefer an interactive report that can be filtered by Type and sorted by incentive total.
Build an email-based incentive report in WPS Spreadsheets
WPS Spreadsheets lets you create a dynamic email summary with UNIQUE, calculate conditional totals with SUMIFS, exclude Parking transactions, and filter the finished report for incentive totals above $600.
- 1. Open the incentive worksheet: Open your workbook in WPS Spreadsheets and identify the Email, Incentive, and Type columns.
- 2. Create a unique email list: Click a blank summary cell and enter =UNIQUE(your_email_range).
- 3. Calculate each qualifying total: In the adjacent cell, use SUMIFS with the email as a criterion and "<>Parking" as the Type criterion.
- 4. Filter the summary results: Select the summary headers, choose Data > Filter, and filter Total Incentives for values greater than 600.
Frequently Asked Questions
How do I exclude Parking from a SUMIFS calculation?
Add the Type range and the criterion "<>Parking" to SUMIFS. For example: =SUMIFS(B2:B1000,A2:A1000,D2,C2:C1000,"<>Parking").
Why does my SUMIFS result include Parking amounts?
Check that the Type range matches the same starting and ending rows as the Incentive and Email ranges. Also verify that the source cells contain Parking exactly, without extra spaces or different spelling.
Can I find totals over $600 without manually filtering?
Yes. If your total is in E2, use =IF(E2>600,"Over $600","") in another column, or use =FILTER(D2:E100,E2:E100>600) where supported.
Do I need UNIQUE before using SUMIFS by email?
UNIQUE is recommended for a clean one-row-per-email summary. SUMIFS can still calculate a total for any individual email entered in a cell.
Can I use these formulas in an Excel workbook opened in WPS Spreadsheets?
Yes. WPS Spreadsheets supports common Excel formulas such as SUMIFS and can open and save XLSX files. UNIQUE requires a version that supports dynamic array functions.
