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:
The value for the SUMPRODUCT across all three sheets turns out to be 355.
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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Use SUMPRODUCT with Values Greater Than Zero in Excel
How to Sum Across Multiple Sheets in Excel
How to Sum Filtered Rows in Excel
