How to Sum All Matching Costs from Another Sheet in WPS Office Excel
Question details
The user needs to total all costs for a product that appears on multiple rows in another worksheet, because XLOOKUP and VLOOKUP only return the first matching value.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an Orders sheet with multiple purchases for the same product and a summary sheet where they need the total cost for a product identifier or name.
- Observed behavior
- XLOOKUP and VLOOKUP return only the first matching order, so they cannot total multiple purchases for the same product.
Open the workbook in WPS Office Spreadsheets and identify the order data worksheet, for example Orders, with the product identifier in one column and the cost in another.
Use SUMIFS or SUMIF to total all matching costs from another sheet
Use this solution when a product appears multiple times in the Orders sheet and you need the combined cost instead of only the first match.
SUMIFS and SUMIF are designed to add every matching value in a range. Unlike XLOOKUP or VLOOKUP, they do not stop at the first match, so they are the correct functions for totaling multiple purchases for the same product.
In WPS Office Spreadsheets, open the summary sheet and click the cell where the total cost should appear.
Type =SUMIFS(Orders!B:B,Orders!A:A,A8) and press Enter. Replace Orders!B:B with the cost column and Orders!A:A with the product column.
If you prefer SUMIF, enter =SUMIF(Orders!A:A,A8,Orders!B:B). This sums the cost column where the product column equals A8.
Drag the fill handle down or double-click it to apply the formula to other product cells in the summary sheet.
Calculate average unit cost from the summed total
Use this solution when you also need the average unit cost after totaling all purchases for the same product.
Sum Matching Costs Across Sheets with WPS Office Spreadsheets
Use WPS Office Spreadsheets to open the same workbook, apply SUMIFS or SUMIF across sheets, and get totals for every matching order. WPS Spreadsheets keeps Excel formula behavior and .xlsx compatibility so you do not need to change your workflow.
- 1. Open the workbook in WPS Office: Launch WPS Office, open the workbook, and switch to the summary sheet where you want the total cost.
- 2. Select the total cell: Click the cell that should display the summed cost for a product.
- 3. Enter the SUMIFS or SUMIF formula: Type =SUMIFS(Orders!B:B,Orders!A:A,A8) or =SUMIF(Orders!A:A,A8,Orders!B:B) and press Enter.
- 4. Adjust sheet and range names: If your sheet name contains spaces, wrap it in single quotes, for example =SUMIFS('Order Data'!B:B,'Order Data'!A:A,A8).
- 5. Apply the formula to remaining products: Use the fill handle to copy the formula down the summary column.
Frequently Asked Questions
Why do XLOOKUP and VLOOKUP not sum multiple matching rows?
XLOOKUP and VLOOKUP are lookup functions. They return the first matching value they find and do not continue adding other matches. To total all matching rows, use SUMIF or SUMIFS instead.
Can I use SUMIF instead of SUMIFS for one condition?
Yes. For a single product condition, use =SUMIF(Orders!A:A,A8,Orders!B:B). SUMIFS also works with one condition and uses the syntax =SUMIFS(Orders!B:B,Orders!A:A,A8).
How do I sum matching costs based on both product and date range?
Use SUMIFS with multiple criteria, for example =SUMIFS(Orders!B:B,Orders!A:A,A8,Orders!C:C,">="&F1,Orders!C:C,"<="&F2), where column C contains order dates and F1 and F2 are the date range cells.
What should I do if the sheet name contains spaces?
Wrap the sheet name in single quotes inside the formula, for example =SUMIFS('Order Data'!B:B,'Order Data'!A:A,A8).
How can I calculate average unit cost after summing all purchases?
Divide the summed total by the purchased quantity using a formula such as =SUMIFS(Orders!B:B,Orders!A:A,A8)/SUMIF(Orders!A:A,A8,Orders!C:C), where column C contains quantity.
