Table of Contents
The SUMPRODUCT function in Excel is a powerful way to sum values from multiple sheets in the same workbook. It allows you to specify a range of cells from multiple sheets and then sum the values in those cells. By using SUMPRODUCT, you can quickly sum values from multiple sheets in one formula, while avoiding the time-consuming process of manually summing each sheet.
The SUMPRODUCT function in Excel returns the sum of the products of two arrays.
You can use the following basic syntax to use the SUMPRODUCT function across multiple sheets:
=SUM(SUMPRODUCT(Sheet1!A2:A11, Sheet1!B2:B11),
SUMPRODUCT(Sheet2!A2:A6, Sheet2!B2:B6),
SUMPRODUCT(Sheet3!A2:A9, Sheet3!B2:B9))
This formula performs a SUMPRODUCT calculation across specific ranges in the sheets named Sheet1, Sheet2, and Sheet3 and then takes the sum of the three values.
The following example show how to use this syntax in practice.
Example: How to Use SUMPRODUCT Across Multiple Sheets
Suppose we have the following three sheets in an Excel workbook:
Sheet1:
Sheet2:
Sheet3:
We can use the following formula to calculate the SUMPRODUCT between columns A and B in each sheet individually and then add the sum of all three values:
=SUM(SUMPRODUCT(Sheet1!A2:A11, Sheet1!B2:B11),
SUMPRODUCT(Sheet2!A2:A6, Sheet2!B2:B6),
SUMPRODUCT(Sheet3!A2:A9, Sheet3!B2:B9))
The following screenshot shows how to use this formula in practice:
If you use the SUMPRODUCT in each sheet individually, you’ll end up with the following values:
- Sheet1 SUMPRODUCT of A and B columns: 184
- Sheet2 SUMPRODUCT of A and B columns: 25
- Sheet3 SUMPRODUCT of A and B columns: 146
The sum of these three values is: 184 + 25 + 146 = 355.
This matches the value that we calculated using one formula.
Note: You can find the complete documentation for the SUMPRODUCT function in Excel .