# How to use the SUMPRODUCT Function Across Multiple Sheets in Excel – 2 Methods

This is an overview.

## The Excel SUMPRODUCT Function

• Purpose

Multiply, and then sum arrays/ranges

• Syntax

=SUMPRODUCT (array1, [array2], …)

• Arguments

array1 – The first array or range to multiply, then add.

array2 – [optional] The second array or range to multiply, then add.

• Return value

The result of the multiplied and summed arrays/ranges

Notes:

If a single array is supplied, the SUMPRODUCT will sum the items in the array. You can supply up to 30 arrays or ranges.

The sample dataset showcases Product and Sales Amount in the 1st 3 months of 2021 in three different sheets.

### Method 1 – Applying the SUMPRODUCT, the SUMIF and the INDIRECT Functions

The INDIRECT Function

• Syntax

INDIRECT(ref_text, [a1]

• Arguments

Ref_text – A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

a1 A logical value that specifies what type of reference is contained in the cell ref_text. If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

The SUMIF Function

• Syntax

SUMIF(range, criteria, [sum_range])

• Arguments

range It defines a range of cells. Cells in each range must be numbers or names, arrays, or references that contain numbers.

criteria The criteria may be a number, expression, cell reference, text, or function. It defines which cells will be added.

Steps:

• Add the sheet names in the Month section in Sheet1.

• Go to C5. and enter the formula:
`=SUMPRODUCT(SUMIF(INDIRECT("'"&\$B\$11:\$B\$13&"'!\$B\$5:\$B\$8"),B5,INDIRECT("'"&\$B\$11:\$B\$13&"'!\$C\$5:\$C\$8")))`
• Press ENTER.

Formula Breakdown:

An absolute reference is used. The sheet names in B11:B13 are listed and used in the formula. B5:B8 is used to match the product name of the reference sheets with the sheets in B5:B8. C5:C8 is used to return the value of the reference sheets.

• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 2 – Using the SUMPRODUCT with the SUM function Across Multiple Sheets

The SUM Function

• Syntax

SUM(number1,[number2],…)

• Argument

number1 The first number to add. It can be a single number, a cell reference or a cell range.

number2 – The second number to add. You can add up to 255 numbers.

Steps:

• Go to C5 in Sheet 2 and enter the SUM and SUMPRODUCT functions.
• Go to the Jan worksheet.

• Select B5:B8.

• Go to Sheet2. and inl B5, close the bracket.
• Enter a multiplication(*) sign.
• Go to the Jan worksheet .
• Select C5:C8.

• Add Feb and Mar sheets and complete the formula. The formula becomes:
`=SUM(SUMPRODUCT((Jan!B5:B8=Sheet2!B5)*(Jan!C5:C8)),SUMPRODUCT((Feb!B5:B8=Sheet2!B5)*(Feb!C5:C8)),SUMPRODUCT((Mar!B5:B8=Sheet2!B5)*(Mar!C5:C8)))`

The SUM function is used to sum the SUMPRODUCT results.

• Press ENTER.
• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Use SUMPRODUCT IF in Excel

Practice here.

## Related Articles

<< Go Back to Excel SUMPRODUCT Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF