How to Use PRODUCT Function in Excel (With 9 Examples)

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.

product function overview

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.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


Introduction to the PRODUCT Function

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


9 Examples to Use the PRODUCT Function in Excel

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. PRODUCT Function with Numerical Values

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:

PRODUCT Function with Numerical Values


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

PRODUCT Function with Cell Reference


3. 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. Multiply Two or More Sums Using SUM Inside PRODUCT Function 

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. Find the Square Root of a Product with SQRT and PRODUCT Functions

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

PRODUCT Function Ignores Empty Cells


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

PRODUCT Function Ignores Texts

 


8. Multiplication of Data Applying 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:

PRODUCT Function with Condition


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


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. And please visit our website Exceldemy to explore more.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo