How to Sum Units Sold by Matching Product Name in WPS Office Spreadsheets
Question details
The user needs to calculate total units sold for each product by matching the product name in one column and summing the corresponding quantity values in another column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a sales worksheet with product names and units sold and wants totals per product rather than a count of matching rows.
- Observed behavior
- Using a counting approach returns how many times a product appears, not the total units sold for that product.
Open your worksheet and identify the column containing product names and the column containing units sold. Ensure product names are consistent, because SUMIF matches text exactly unless you add wildcards.
Use SUMIF to sum units sold for one product name
Use this when you need a formula-based total for a specific product name and want to keep cell references easy to update.
SUMIF checks a range of product names and adds the related cell from the units sold column only when the product name matches your criteria. The criteria can be typed directly or taken from a cell, making it quick to change the product name.
Click an empty cell where the total units sold for the product should appear.
Type =SUMIF($C$185:$C$201,C199,$D$185:$D$201) and press Enter. Replace $C$185:$C$201 with your product name range, C199 with the product name to match, and $D$185:$D$201 with the units sold range.
If you have a list of product names in column C, drag the fill handle down to copy the formula and sum units for each product.
Use a PivotTable to sum units sold for all product names
Use this when you want a complete product sales summary in one table without writing formulas for each product.
Sum Units Sold by Product Name in WPS Office Spreadsheets
WPS Office Spreadsheets provides the same SUMIF, SUMIFS, and PivotTable features that make it simple to total units sold by matching product names. You can open .xlsx files, keep existing formulas and formatting, and get the result without changing your workflow.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook containing your product names and units sold.
- 2. Enter a SUMIF formula for a specific product: Select a blank cell, type =SUMIF($C$185:$C$201,C199,$D$185:$D$201), and press Enter to total the matching units.
- 3. Create a PivotTable for all products: Select the data range, go to Insert > PivotTable, then add Product Name to Rows and Units Sold to Values to summarize every product.
- 4. Save the result in Excel format: Press Ctrl+S and keep .xlsx so the summary remains compatible with Microsoft Excel users.
Frequently Asked Questions
What is the difference between COUNTIF and SUMIF for matching product names?
COUNTIF only counts the rows where the product name matches. SUMIF adds the values in the units sold column for those matching rows, which gives total units sold.
Can I use a cell reference as the criteria in SUMIF?
Yes. For example, =SUMIF($C$185:$C$201,C199,$D$185:$D$201) takes the product name from cell C199, so you can change the cell and the formula updates automatically.
How do I sum units sold when the product name is only part of the cell text?
Use wildcards in the criteria. For example, =SUMIF($C$2:$C$100,"*Widget*",$D$2:$D$100) adds units for any cell in column C that contains Widget.
Why does my SUMIF return 0 even though matching products exist?
Check for trailing spaces, inconsistent text, or numbers stored as text. The criteria and product name cells must match after Excel trims spaces, or a wildcard can help when text is not exact.
