Wasim Akram
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Methods to Multiply from Different Sheets in Excel
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.
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!C5Where,
- 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.
Read More: How to Use Multiply Sign in Excel (With 3 Alternative Methods)
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.
Read More: If Cell Contains Value Then Multiply Using Excel Formula (3 Examples)
Similar Readings
- How to Multiply Rows in Excel (4 Easiest Ways)
- Multiply Matrices in Excel (2 Easy Methods)
- Multiplication Formula in Excel (6 Quick Approaches)
- How to Multiply a Column by a Number in Excel (4 Easy Methods)
- What is the Formula for Multiplication in Excel for Multiple Cells? (3 Ways)
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.
- Here, you can see we have calculated the total salary of the employee while collecting data from another sheet.
Read More: How to Multiply Columns in Excel (9 Useful and Easy Ways)
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.
Read More: How to Multiply One Cell by Multiple Cells in Excel (4 Ways)
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 combinedly for this is an array formula
Thus we can multiply in different sheets with an array formula.
Read More: How to Multiply Multiple Cells in Excel (4 Methods)
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!
