Table of Contents
“SUMPRODUCT is a useful function in Excel that allows for the calculation of the product of corresponding values in multiple arrays, with the option to apply additional conditions. To use SUMPRODUCT across multiple sheets in Excel, simply select the cells or ranges from different sheets that you want to include in the calculation, making sure to separate the references with a comma. You can also use named ranges or 3D references to include data from multiple sheets. This function is particularly helpful for analyzing and summarizing data from multiple sources, providing a comprehensive view of the data across different sheets.”
Excel: Use SUMPRODUCT Across Multiple Sheets
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 .
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I use SUMPRODUCT across multiple sheets in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-use-sumproduct-across-multiple-sheets-in-excel/
stats writer. "How do I use SUMPRODUCT across multiple sheets in Excel?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-use-sumproduct-across-multiple-sheets-in-excel/.
stats writer. "How do I use SUMPRODUCT across multiple sheets in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-use-sumproduct-across-multiple-sheets-in-excel/.
stats writer (2024) 'How do I use SUMPRODUCT across multiple sheets in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-use-sumproduct-across-multiple-sheets-in-excel/.
[1] stats writer, "How do I use SUMPRODUCT across multiple sheets in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I use SUMPRODUCT across multiple sheets in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.