How to Isolate QM Payments and Calculate Their Total in WPS Spreadsheets
Question details
The user needs to isolate payments categorized as QM for a specific group of 286 owners and calculate the combined payment total from a spreadsheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a payment dataset with a category column and an owner column, and wants to total QM payments only for owners on a defined list.
- Observed behavior
- The user needs a formula- or filter-based total that reflects only the selected QM and owner criteria.
Confirm your payment data is in a single table with separate columns for owner, category, and payment amount, and that the 286 owner IDs in your list exactly match the owner values in the data.
Use SUMPRODUCT with MATCH to sum QM payments for the 286-owner list
Best when you need one formula that combines both the QM category and a separately maintained owner list without adding helper columns.
This formula multiplies the payment amounts by two TRUE/FALSE conditions: the category must be QM, and the owner must be found in the specified owner list.
Open your workbook in WPS Spreadsheets and identify the columns used for owner, category, and payment amount. For example, Owner in column B, Category in column C, and Payment in column P.
Place the 286 owner IDs in a separate range such as Owners!A2:A287, or select the existing owner-list range on your sheet.
Click an empty result cell, type =SUMPRODUCT(P2:P1000*(C2:C1000="QM")*ISNUMBER(MATCH(B2:B1000,Owners!A2:A287,0))) and press Enter.
Spot-check a few matching owner rows and confirm that the returned total equals the sum of the payment amounts for QM rows on the owner list.
Use SUMIF to total all QM payments without owner filtering
Use this quick formula when you only need the total for every row where the category is QM.
Calculate QM payment totals for a defined owner list in WPS Spreadsheets
Use WPS Spreadsheets to run Excel-compatible formulas that isolate QM payments and calculate the combined total for your specified 286 owners.
- 1. Open the payment workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the Excel file containing the payment data.
- 2. Enter the calculation formula: Click the result cell, type the SUMIF or SUMPRODUCT formula for your QM and owner criteria, and press Enter.
- 3. Review and save the report: Verify the total, then go to Menu > Save As and choose .xlsx to keep the workbook in an Excel-compatible format.
Frequently Asked Questions
Can I calculate QM payments if I do not need the 286-owner filter?
Yes. Use =SUMIF(C2:C1000,"QM",P2:P1000), where C is the category column and P is the payment amount column.
How do I sum payments only for owners on a separate list?
Use =SUMPRODUCT(P2:P1000*(C2:C1000="QM")*ISNUMBER(MATCH(B2:B1000,Owners!A2:A287,0))). Adjust the ranges to match your workbook.
Does WPS Spreadsheets support SUMIFS and SUMPRODUCT like Excel?
Yes. WPS Spreadsheets supports these Excel-compatible functions in .xlsx workbooks, so you can use the same formulas.
Can I filter first and total visible rows instead of writing a formula?
Yes. Apply the QM and owner filters or use Advanced Filter, then enter =SUBTOTAL(9,P2:P1000) in a cell below the visible payment amounts.
