How to Calculate Case Worker Totals with SUMPRODUCT in WPS Office Spreadsheets
Question details
The user needs to calculate the total number of adults or children assigned to each case worker from a table where an x marks the assigned worker, using formulas that can update as new applications are entered.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A case-management workbook contains case worker names across A2:C2, x marks in A3:C8, adult counts in D3:D8, and child counts in E3:E8. The user wants totals for the selected worker in H1.
- Observed behavior
- The workbook should return live adult and child totals for the selected case worker and continue to calculate correctly when new application rows are added.
Verify that case worker names are in A2:C2, assignment marks are in A3:C8, adult counts are in D3:D8, and child counts are in E3:E8, with the selected worker name in H1. If your data will grow, identify the current last row so you can extend the formula ranges or convert the range to an Excel Table.
Use SUMPRODUCT to total adults and children for the selected case worker
Use this solution when your workbook has case worker names in A2:C2, x marks in the data area, and adult/child counts in columns D and E, and you need totals that recalculate when H1 changes.
The formula multiplies three conditions: which assignment cells contain x, which column matches the worker name in H1, and the corresponding adult or child count. SUMPRODUCT then sums the resulting values.
If new application rows are added, extend the ranges beyond row 8 or convert the range to an Excel Table and use structured references so the totals continue to include new data.
Click cell H1 and type the name of the case worker exactly as it appears in A2:C2. This cell is used as the SUMPRODUCT criteria.
Select the cell where the adult total should appear, paste =SUMPRODUCT(($A$3:$C$8="x")*($A$2:$C$2=$H$1)*$D$3:$D$8), and press Enter.
Select the cell where the child total should appear, paste =SUMPRODUCT(($A$3:$C$8="x")*($A$2:$C$2=$H$1)*$E$3:$E$8), and press Enter.
If new application rows are added below row 8, change 8 to the new last row in all ranges. For a continuously growing table, select the data range and press Ctrl+T to format it as an Excel Table, then use table structured references.
Calculate case worker totals directly in WPS Office Spreadsheets
Use the same SUMPRODUCT formulas in WPS Office Spreadsheets to total adult and child counts by selected case worker. WPS Spreadsheets supports Excel formulas, cell references, and Excel Table structured references, so your case-management workbook calculates live totals as new applications are added.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select your existing .xlsx or .xls case-management workbook.
- 2. Select the adult result cell: Click the cell where the adult total should appear, paste the adult SUMPRODUCT formula into the formula bar, and press Enter.
- 3. Select the child result cell: Click the child total cell, paste the child formula that references column E instead of column D, and press Enter.
- 4. Test the result by changing H1: Type another case-worker name in H1 and confirm that the adult and child totals recalculate automatically.
Frequently Asked Questions
Can I use this formula if my case-worker names are in a different row or column?
Yes. Adjust A2:C2, A3:C8, D3:D8, and E3:E8 to match your actual layout. Keep the criteria range and the adult or child sum range aligned by row so SUMPRODUCT calculates the correct totals.
Why does the formula return 0 or #VALUE!?
Make sure the worker name in H1 matches exactly, including spelling and spaces, and that the assignment mark is a lowercase x as used in the criteria. If your Excel version returns an array-size error, use helper columns or Excel Table structured references to keep ranges consistent.
How do I make the totals update automatically when new applications are added?
Extend the formula ranges beyond the current last row, or select the data range and press Ctrl+T to convert it to an Excel Table. Then use table structured references so rows added below the table are included automatically.
Does this SUMPRODUCT formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports SUMPRODUCT, cell references, and Excel Table structured references, so you can open the same workbook and use the same formulas without changing your file format.
