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

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel multiplying values is one of the most frequently used functions. There are several ways to perform this. But 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. For more information, visit this link.


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.

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

Multiply from Different Sheets in Excel


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.

Use Multiplication Symbol to Multiply from Different Sheets

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

Apply PRODUCT Function to Multiply from Different Sheets

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

Read More: If Cell Contains Value Then Multiply Using Excel Formula


Similar Readings

  1. Multiplication Formula in Excel 

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 per day salary of an employee in “Sheet 5”. We will calculate the total salary of the employee in 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.

Apply PRODUCT Function to Multiply from Different Sheets

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

Perform SUMPRODUCT Function to Multiply in Different Sheets

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

Use an Array Formula to Multiply in Different Sheets

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

Use an Array Formula to Multiply in Different Sheets

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 combinedly for this is an array formula

Use an Array Formula to Multiply in Different Sheets

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

Read More: How to Multiply Multiple Cells in Excel


Download Practice Workbook

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


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!


Related Articles

Wasim Akram
Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo