How to Use SUMPRODUCT Across Multiple Sheets in Excel

SUMPRODUCT is a versatile function in Excel that multiplies the corresponding components in the given arrays and returns the sum of those products. To use SUMPRODUCT across multiple sheets, you'll need to use a combination of the SUMPRODUCT function and 3D referencing.

Here's how to use SUMPRODUCT across multiple sheets in Excel:

1. Organize your data: Ensure that the data you want to use the SUMPRODUCT function on is organized in the same layout across all the sheets.
2. Use a helper column: You'll need to create a helper column on a summary sheet (a separate sheet where you want the final SUMPRODUCT result to appear) to list the names of the sheets you want to perform the SUMPRODUCT function on.
3. Use the INDIRECT function: The INDIRECT function will be used to dynamically refer to the sheet names listed in the helper column.
4. Combine SUMPRODUCT and INDIRECT: Combine the SUMPRODUCT and INDIRECT functions to perform the calculation across multiple sheets.

Here's an example to demonstrate the process:

Example

Let's say we have three sheets named "Sheet1", "Sheet2", and "Sheet3" with the following data:

We want to calculate the total sales (Quantity * Price) for each product across all three sheets.

1. Create a new sheet called "Summary" and list the sheet names (Sheet1, Sheet2, and Sheet3) in column A (A2:A4).
2. In cell B1, write the following formula:

=SUMPRODUCT(INDIRECT("'"&A2&"'!B2:B4"), INDIRECT("'"&A2&"'!C2:C4")) + SUMPRODUCT(INDIRECT("'"&A3&"'!B2:B4"), INDIRECT("'"&A3&"'!C2:C4")) + SUMPRODUCT(INDIRECT("'"&A4&"'!B2:B4"), INDIRECT("'"&A4&"'!C2:C4"))

Let's break down the formula:

• INDIRECT("'"&A2&"'!B2:B4") and INDIRECT("'"&A2&"'!C2:C4"): These parts of the formula use the INDIRECT function to refer to the Quantity and Price ranges in each sheet.
• SUMPRODUCT(INDIRECT(...), INDIRECT(...)): This part of the formula calculates the SUMPRODUCT of the Quantity and Price ranges for each sheet.
• The + signs in between each SUMPRODUCT function add the results of the SUMPRODUCT calculations for each sheet.

3. Press Enter to get the result. The total sales across all three sheets will be calculated and displayed in cell B1.

That's it! You've successfully used the SUMPRODUCT function across multiple sheets in Excel.