Function Problems

Sum Product Values Across Sheets in Excel and WPS Spreadsheets

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Total values for each product across two distributor worksheets by matching product names to column headers.

Sum Product Values Across Sheets in Excel and WPS Spreadsheets
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.
Before you start

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.

Solution 1Recommended

Sum matching product columns with SUMPRODUCT

Use this method to add all values beneath matching product headers on both distributor sheets.

1
Select the result cell

On the Total sheet, select B2 next to the product name in A2.

2
Enter 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.

3
Fill down

Drag the fill handle from B2 down alongside the remaining product names in column A.

Sum matching product columns with SUMPRODUCT
Check the value ranges: Each header range must have the same number of columns as its corresponding value range. Text in the value ranges can cause a #VALUE! error.
Sum products across worksheets

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. 1. Open the workbook: Open the workbook in WPS Spreadsheets and check the Distributor1, Distributor2, and Total sheet tabs.
  2. 2. Select a result cell: On the Total sheet, select B2 next to the product name in A2.
  3. 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. 4. Calculate other products: Drag the fill handle from B2 down to apply the formula to the other product names.
Total matching product columns across multiple sheetsOpen and edit Microsoft Excel XLSX and XLS filesFill the formula down for a list of productsWork with familiar spreadsheet formulas and sheet references
QA img-9

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.