Sum Product Values Across Sheets in Excel and WPS Spreadsheets
Question details
Total values for each product across two distributor worksheets by matching product names to column headers.

- Product
- WPS Spreadsheets and Microsoft Excel
- Device & OS
- not provided
- Scenario
- A Total sheet lists product names, while Distributor1 and Distributor2 contain product headers and values in separate worksheets.
- Observed behavior
- The desired result is a combined total for each product on the Total sheet.
Check that Distributor1 and Distributor2 have product headers in G2:J2 and numeric values in G3:J19. Confirm that Total!A2 contains a product name that matches the headers; adjust the sheet names and ranges in the formulas if your layout differs.
Sum matching product columns with SUMPRODUCT
Use this method to add all values beneath matching product headers on both distributor sheets.
On the Total sheet, select B2 next to the product name in A2.
Enter =SUMPRODUCT((Distributor1!$G$2:$J$2=Total!A2)*Distributor1!$G$3:$J$19)+SUMPRODUCT((Distributor2!$G$2:$J$2=Total!A2)*Distributor2!$G$3:$J$19) and press Enter.
Drag the fill handle from B2 down alongside the remaining product names in column A.

Add distributor totals with INDEX and MATCH
Use this method when each distributor sheet already has a total for each product in row 20.
Calculate distributor product totals in WPS Spreadsheets
Use SUMPRODUCT in WPS Spreadsheets to match each product name and add its values across distributor worksheets.
- 1. Open the workbook: Open the workbook in WPS Spreadsheets and check the Distributor1, Distributor2, and Total sheet tabs.
- 2. Select a result cell: On the Total sheet, select B2 next to the product name in A2.
- 3. Add the formula: Enter =SUMPRODUCT((Distributor1!$G$2:$J$2=Total!A2)*Distributor1!$G$3:$J$19)+SUMPRODUCT((Distributor2!$G$2:$J$2=Total!A2)*Distributor2!$G$3:$J$19) and press Enter.
- 4. Calculate other products: Drag the fill handle from B2 down to apply the formula to the other product names.

Frequently Asked Questions
Which formula should I use if there is no total row?
Use the SUMPRODUCT formula in Total!B2. It sums the matching columns directly from G3:J19 on each distributor sheet.
Why does the INDEX and MATCH formula return #N/A?
The product name in Total!A2 may be missing or spelled differently in one of the distributor header ranges. Check G2:J2 on both sheets for matching names and extra spaces.
What happens if a product header appears more than once?
SUMPRODUCT adds values from every matching column in the specified range. INDEX and MATCH returns the total from only the first matching header on each sheet.
How do I include a third distributor sheet?
Add another SUMPRODUCT term to the formula, such as +SUMPRODUCT((Distributor3!$G$2:$J$2=Total!A2)*Distributor3!$G$3:$J$19), adjusting the ranges to match that sheet.




