SUMPRODUCT Across Multiple Sheets in Excel (2 Methods)

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 how to use the SUMPRODUCT function across multiple sheets. So, let’s begin.

Overview Image

Introduction to Excel SUMPRODUCT Function

The 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

  • 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 multiplied and summed arrays/ranges


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

Read More: How to Use SUMPRODUCT with Criteria in Excel

SUMPRODUCT Across Multiple Sheets in Excel: 2 Methods

We take a data set of a stationary shop 1st 3 months of 2021 in three different sheets. Here, we have used the Microsoft Excel 365 version.

Dataset for sumproduct across multiple sheets

1. Applying with SUMIF and INDIRECT Functions

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

Introduction to 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 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, cell reference, text, or function. It defines which cells will be added.


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


  • Firstly, modify the data set. add the sheet names in the Month section on Sheet1.

Modify dataset for sumproduct across multiple sheets in Excel

  • Secondly, go to cell C5. and write down the formula. So, the formula is:
  • Consequently, press the ENTER button.

Using SUMIF and INDIRECT function for SUMPRODUCT across multiple sheets in excel

Formula Breakdown:

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.

  •  Finally, pull down the Fill Handle icon from cell C5 to C8.

Fill handle

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

Read More: How to Use SUMPRODUCT Function with Multiple Columns in Excel

2. SUMPRODUCT with SUM function Across Multiple Sheets

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

Introduction to SUM Function

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

  • Syntax


  • 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 Function

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


  • Firstly, go to cell C5 of Sheet 2 and write the SUM and SUMPRODUCT functions.
  • Secondly, we will go to the worksheet Jan.

Applying SUMPRODUCT with SUM across multiple sheets in Excel

  • Moreover, after going to sheet Jan, select range B5 to B8.

  • However, move to Sheet2. and compare to cell B5.
  • Apparently, close the bracket.
  • Sequentially, put a multiplication(*) sign. Thus, again go to the worksheet Jan.
  • Eventually, select the range C5:C8.

adding one sheet for sumproduct across multiple sheets

  • Similarly, add Feb and Mar sheets and complete the formula. So, the formula becomes:

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.

SUM function

  • Finally, press ENTER. and drag down the Fill Handle icon from cell C5 to C8 and get the final return.

Now, get the result for all the cells.

Final output for sumproduct across multiple sheets in excel

Read More: How to Use SUMPRODUCT IF in Excel

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice section

Download Practice Workbook

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


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

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
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

Leave a reply

Advanced Excel Exercises with Solutions PDF