Excel is the most popular application mostly for official purposes. Professionals who work with large amounts of data easily rely on Excel. With the help of Excel, we can organize data smoothly. Lots of functions are used in Excel for different purposes. This article is all about **the SUMPRODUCT function**. We will discuss **SUMPRODUCT **across multiple sheets.

We take a data set of a stationary shop 1st 3 months of 2021 in three different sheets.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Introduction to the Excel SUMPRODUCT Function**

The Excel **SUMPRODUCT **function multiplies the cells of range or arrays and sums them together. If we elaborate 1st it will multiply the elements of a range. Then it will give a sum of the multiplied elements.

**Purpose**

Multiply, then sum arrays/ranges

**Return value**

The result of multiplied and summed 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.

**Notes:**

The **SUMPRODUCT **function multiplies arrays or ranges together and returns the sum of products. If a single array is supplied, **SUMPRODUCT **will simply sum the items in the array. We can supply up to 30 arrays or ranges.

At the first glance, **SUMPRODUCT **may seem boring, complex, and even pointless. But **SUMPRODUCT **is an amazingly versatile function with many uses. Because it will control arrays nicely. We can use it to process ranges of cells in clever, elegant ways.

In this topic, we will use **SUMPRODUCT **across multiple sheets in Excel. We will discuss two methods in this regard. All the processes will be shown here.

**2 Methods to Use SUMPRODUCT Across Multiple Sheets in Excel**

**1. SUMPRODUCT with SUMIF and INDIRECT Function Across Multiple Sheets**

In this section, we will use the **INDIRECT **and **SUMIF **functions along with the **SUMPRODUCT **function.

**Introduction to the INDIRECT Function**

**The INDIRECT function** returns the reference specified by a text string. References are immediately evaluated to display their contents. We use it when we want to change the reference to a cell within a formula without changing the formula itself.

**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.

**Introduction to the SUMIF Function**

**The SUMIF function** adds the cells specified by a given condition or criteria.

**Syntax**

**SUMIF(range, criteria, [sum_range])**

**Arguments**

** range –** It defines the range of cells that we want. Cells in each range must be numbers or names, arrays, or references that contain numbers.

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

**SUMPRODUCT with INDIRECT & SUMIF**

Now, we will show the process of **SUMPRODUCT **with the **INDIRECT **function across multiple sheets.

**Step 1:**

- Modify the data set. Add the sheet names in the Month section on
**Sheet1**.

**Step 2:**

- Go to
**Cell C5**. - Write down the formula. So, the formula is:

`=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$11:$B$13&"'!$B$5:$B$8"),B5,INDIRECT("'"&$B$11:$B$13&"'!$C$5:$C$8")))`

**Step 3:**

- Now, press the
**ENTER**button.

**Step 4:**

- Pull down the
**Fill Handle**icon from**Cell****C5**to**C8.**

Now, get the final result. It is the sum of all three months of different elements.

**Note:**

In the formula, we used the absolute reference so that the reference doesn’t change. We listed the sheet names from **B11 **to **B13** and used them in the formula. Range** B5 to B8** is used to match the product name of the reference sheets with present sheets cell **B5 to B8**. And **C5 to C8** is used to return the value of the reference sheets.

**Read More:** **[Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel**

**2. SUMPRODUCT with the SUM function Across Multiple Sheets**

**SUMPRODUCT **can be applied with **the SUM function** across multiple sheets.

**Introduction to the SUM Function**

**The SUM function** adds values. We can add individual values, cell references or ranges, or a mix of all three.

**Syntax**

**SUM(number1,[number2],…)**

**Argument**

** number1 – **This is the first number we want to add. This can be any single number, cell reference, or cell range.

* number2 *– This is the second number that we want to add. We can add up to 255 numbers in this way.

**SUMPRODUCT with SUM**

Now, we will show the steps to apply **SUMPRODUCT** with **SUM** across multiple sheets.

**Step 1:**

- Go to
**Cell C5**of**Sheet2**. - Write the
**SUM**and**SUMPRODUCT**functions. - Then we will go to sheet
**Jan**.

**Step 2:**

- We go to sheet
**Jan**. - Select range
**B5 to B8.**

**Step 3:**

- Go to
**Sheet2.** - Compare to
**Cell B5.**

**Step 4:**

- Now, close the bracket.
- Put a
**multiplication(*) sign.**

**Step 5:**

- Again go to sheet
**Jan.** - And select the range
**C5 to C8.**

**Step 6:**

- In the same way, add
**Feb**and**Mar**sheets and complete the formula**.**So, 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)))`

**Step 7:**

- Now, press
**Enter.**

**Step 8:**

- Pull down the
**Fill Handle**icon from**Cell****C5**to**C8**and get the final return.

Now, get the result for all the cells.

**Note:**

Here, the **SUM **function is used to sum the **SUMPRODUCT **results. First, we find out the **SUMPRODUCT **for each sheet. Then we add them using the **SUM **function.

**Read More: ****SUMPRODUCT Multiple Criteria in Excel(3 Approaches)**

**Conclusion**

Here, we’ve shown the **SUMPRODUCT **across multiple sheets using other functions by two methods. Hope this will help to get your solution. If you think we need any modification, you can tell us in the comment section.