How to Multiply from Different Sheets in Excel (5 Methods)

In Microsoft Excel multiplying values is one of the most frequently used functions. There are several ways to perform this. However, it becomes critical to do this when the values are on different sheets. Well, it won’t be a problem from today. In this article, I am going to explain some easy ways to multiply from different sheets in Excel.

How to Multiply from Different Sheets in Excel: 5 Methods

In this article, I am going to explain 5 quick methods to multiply from different sheets.

Suppose we have a dataset, In “Sheet 1” we have employee names and their salary per day. In “Sheet 2” we have their total working days in a month. Now we are going to multiply data from these two different sheets.

1. Use Multiplication Symbol to Multiply from Different Sheets

In this method, I am going to calculate each employee’s total salary using a multiplication symbol.

Steps:

Select a cell (F5) to show output.
Apply the formula-

=Sheet2!C5*Sheet1!C5

Where,

The Asterisk sign(*) multiplies values.

Press Enter.
Drag down the Fill handle to get the result in all cells.

Here you can see we have successfully multiplied data from two different sheets.

2. Apply PRODUCT Function to Multiply from Different Sheets

We can multiply from different sheets by using the PRODUCT Function too. Let’s see!

Steps:

Select a cell for output. Here I have selected cell (F5).
Apply the formula-

=PRODUCT(C5, Sheet3!C5)

Where,

The PRODUCT function multiplies all the numbers given as arguments and returns the product.
(C5, Sheet3!C5)= It is the data range from two sheets.

Click Enter.
Drag down the Fill handle to apply the formula in every cell.

Thus we can multiply from different sheets very easily using function.

3. Multiply Multiple Columns from Different Sheets

Now, in this method, I am going to explain how you can multiply multiple columns from different sheets.

Suppose we have a dataset containing the per day salary of an employee in “Sheet 5”. We will calculate the total salary of the employee on a different sheet.

Steps:

Select a cell to apply the formula. Here I have selected cell (C5).
Apply the formula-

=5*SUM(Sheet5!D5:D9)

Where,

The SUM function sums up all the values in the range.
“5” is used because it contains 5 days of data.
Press Enter.

Here, you can see we have calculated the total salary of the employee while collecting data from another sheet.

4. Perform SUMPRODUCT Function to Multiply in Different Sheets

In this following method, I am going to explain multiplying in a different sheet using the SUMPRODUCT function. The SUMPRODUCT function is the perfect function to multiply from different sheets in Excel.

Steps:

Select a cell (C12) for the result.
Apply the formula-

=SUMPRODUCT(C5:C10, Sheet7!C5:C10)

Where,

The SUMPRODUCT function returns the sum of the products in the range.
(C5:C10, Sheet7!C5:C10)= It is the range.

Press Enter.

Thus we can multiply from different sheets and get the total value in a single cell.

5. Use an Array Formula to Multiply in Different Sheets

With the Array formula, we can also multiply in different sheets.

In the dataset, we will calculate the maximum and minimum salaries of employees.

Step 1:

Select cell (C12).
Apply the formula-

=MAX(C5:C10*Sheet9!C5:C10)

Where,

The MAX function returns the largest value from the range.
(C5:C10*Sheet9!C5:C10)= It is the range.

As this formula is an array formula, we need to press Ctrl+Shift+Enter simultaneously to get the output.

Here, we got our maximum salary. Now, let’s calculate the minimum salary.

Step 2:

Select cell (C13).
Apply the formula-

=MIN(C5:C10*Sheet9!C5:C10)

Where,

The MIN function returns the smallest value from the range.
(C5:C10*Sheet9!C5:C10) = It is the selected range.

Click Ctrl+Shift+Enter combined for this is an array formula

Thus we can multiply in different sheets with an array formula.

Download Practice Workbook

Multiply from Different Sheets.xlsx

Things to Remember

When applying an array formula press Ctrl+Shift+Enter Combinedly.
“#VALUE”– This may appear when the value is not appropriate or there are problems with the referencing.

Conclusion

I have tried to cover all the methods of multiplying from different sheets in excel. Hope you find it useful. If you find any problems regarding solving problems feel free to share us in the comment section. Thanks!