The PRODUCT function is used to multiply numbers in Excel. Usually, you can multiply numbers using the product sign (*) in-between numbers. But that method may not be convenient in all types of situations. Especially, when you need to work with tons of numbers, the PRODUCT function comes up with more flexibility. In this regard, go through the whole article. Because you will learn to use the PRODUCT function in Excel with 9 suitable examples.
The above screenshot is an overview of the article, representing a few applications of the PRODUCT function in Excel. You’ll learn more about the methods along with the other functions to use the PRODUCT function precisely in the following sections of this article.
PRODUCT Function in Excel: Syntax
- Function Objective:
The PRODUCT function is used to calculate the multiplication among numbers in Excel.
- Syntax:
=PRODUCT(number1, [number2], …)
- Arguments Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| number1 | Required | The first number of the range of numbers that you want to multiply. |
| number2 | Optional | Extra numbers or range of numbers that you want to multiply. |
- Return Parameter:
Multiplied value of the given numbers within the argument field.
The PRODUCT function accepts arguments in numerous orders. It can also work with other functions too. We are going to discuss all of them one by one in the following sections. So without having any further discussion let’s jump straight into all of them now.
1. Using PRODUCT Function with Numerical Values in Excel
We can use the PRODUCT function to multiply values in the very traditional way of multiplying. What we do in our traditional way is add the multiplication sign in-between numbers.
For example, we have two numbers. Now we can multiply them both simply by putting the product sign (*) in between them i.e. 5*8 for instance.
We will implement this using the PRODUCT function below. Just go to the following steps.
🔗 Steps:
❶ Select cell E5 ▶ to store the formula result.
❷ Insert the formula within the cell.
=PRODUCT(4*4*7)❸ Press the ENTER button.
Now you can see the end result as in the picture below:
2. Applying PRODUCT Function with Cell Reference
Another way that you can follow is to multiply values stored within cells. This time you will specify the cell references separated by a comma inside the PRODUCT function argument field.
Now follow the steps below to learn the whole procedure:
🔗 Steps:
❶ First click on cell E5 ▶ to store the formula result.
❷ Insert the formula within the cell.
=PRODUCT(B5,C5,D5)❸ Hit the ENTER button.
❹ At Last drag the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
3. Using the Excel PRODUCT Function with Numbers in Different Ranges
This method will save you time when you have to work with a huge amount of data. Now you will use a range of numbers instead of typing all of the cell references manually one by one.
This time you will specify the range of the number, then Excel will automatically extract the numbers and then multiply them all together.
You can use multiple series of numbers. All you need to do is separate them all using a comma in between the ranges. That’s it.
Now follow the steps below, these will guide you to use a range of numbers instead of typing all the cell addresses manually. Which is tedious as well as time-consuming.
🔗 Steps:
❶ First select cell E5 ▶ to store the formula result.
❷ Insert the formula within the cell.
=PRODUCT(B5:B11,C5:C11)❸ Now press the ENTER button.
❹ Finally drag the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
4. Combining SUM and PRODUCT Functions to Multiply Two or More Sums
The PRODUCT function facilitates us to incorporate other functions inside it. This feature makes you do more powerful stuff using this function.
In this section, we will use the SUM function inside the PRODUCT function.
Our purpose is to perform the multiplication over the summation of a range of numbers. Now follow the steps below and you will learn to use this function.
🔗 Steps:
❶ Click on cell E5 ▶ to store the formula result.
❷ Enter the formula within the cell.
=PRODUCT(SUM(B5:B14),SUM(C5:C14))❸ Hit the ENTER button.
❹ Now drag the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
5. Finding the Square Root of a Product with SQRT and PRODUCT Functions in Excel
We’ve seen in the previous section that we can use other functions inside the PRODUCT function. In this section, we will show you how to use the PRODUCT function inside another function in Excel.
For example, we want to calculate the square root of a multiplied series of numbers. We can easily calculate the square root of numbers using the SQRT function.
Now you are recommended to follow the steps below that will guide you to use the PRODUCT function inside the SQRT function in Excel.
🔗 Steps:
❶ First of all select cell E5 ▶ to store the formula result.
❷ Insert the formula within the cell.
=SQRT(PRODUCT(B5:D5))❸ Now press the ENTER button.
❹ Drag the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
6. Using PRODUCT Function with Data That Have Empty Cells
One amazing thing about the PRODUCT function is that it can ignore the blank cells within the specified range. It only counts cells having numerical values in them.
🔗 Steps:
❶ Click on cell E5 ▶ to store the formula result.
❷ Input the formula within the cell.
=PRODUCT(B5:D5)❸ Press the ENTER button.
❹ Draw the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
7. Applying Excel PRODUCT Function with Data That Have Text in It
Another amazing thing about the PRODUCT function is that it can ignore the cells having text within the specified range. It only counts cells having numerical values in them.
🔗 Steps:
❶ At first select cell E5 ▶ to store the formula result.
❷ Enter the formula:
=PRODUCT(B5:D5)❸ Hit the ENTER button.
❹ At this stage, drag the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
8. Using PRODUCT Function to Multiply Data based on Condition
Now we will use the PRODUCT Function with Condition. All we want to do, if we find any blank cell in a row, we won’t calculate the product of that row. To do so,
🔗 Steps:
❶ Select cell E5 ▶ to store the formula result.
❷ Now insert the formula:
=IF(OR(C5="",D5=""),"",PRODUCT(B5:D5))❸ After that press the ENTER button.
❹ Pull the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
9. Using PRODUCT Function to Multiply Two or More Outputs of VLOOKUP Function
We can use the PRODUCT function along with the VLOOKUP Function. Follow the steps below that will guide you to do so.
🔗 Steps:
❶ Firstly click on cell E5 ▶ to store the formula result.
❷ Type the formula within the cell.
=PRODUCT(VLOOKUP(H5,$B$5:$C$12,2,0), VLOOKUP(H5,$E$5:$F$12,2,0))❸ Hit the ENTER button.
❹ Finally drag the Fill Handle icon to the end of the Product column.
When you are done with all the steps as mentioned above, you will see the end result as in the picture below:
Formula Breakdown:
- (VLOOKUP(H5,$B$5:$C$12,2,0) ▶ Looks for units in the table range B5:C12.
- VLOOKUP(H5,$E$5:$F$12,2,0) ▶ Looks for cost per unit in the table range E5:F12.
- =PRODUCT(VLOOKUP(H5,$B$5:$C$12,2,0), VLOOKUP(H5,$E$5:$F$12,2,0)) ▶ multiples the number of unit and cost per unit returns from the two VLOOKUP functions.
Things to Remember
📌 You can insert a maximum of 255 arguments inside the PRODUCT function at a time.
📌 If all the reference cells contain text only, the PRODUCT function will return the #VALUE error.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
Conclusion
To sum up, we have discussed the usage of the Excel PRODUCT function with 9 suitable examples. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
