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

Get FREE Advanced Excel Exercises with Solutions!

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.

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:

PRODUCT Function with Numerical Values


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:

PRODUCT Function with Cell Reference


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:

PRODUCT Function Ignores Empty Cells


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:

PRODUCT Function Ignores Texts

 


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:

PRODUCT Function with Condition


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

2 Comments
  1. HI THERE!

    4. Combining SUM and PRODUCT Functions to Multiply Two or More Sums

    THIS EXAMPLE CASE IS UNABLE TO UNDERSTAND THE RESULTING VALUES.
    WOULD REQUEST A CLARIFICATION WITH SLIGHTLY DEEPER INTERPRETATION….

    • Reply Avatar photo
      Md. Abdur Rahim Rasel Jan 8, 2024 at 3:32 PM

      Hello UMESH!
      Thanks for reaching out and sharing your problem with us.
      let’s break down the given formula =PRODUCT(SUM(B5:B14),SUM(C5:C14)) and provide a deeper interpretation:

      SUM(B5:B14): calculates the sum of the values in the range from cell B5 to B14. It adds up all the numbers in that specified range.

      SUM(C5:C14): calculates the sum of the values in the range from cell C5 to C14. It adds up all the numbers in that specified range.

      =PRODUCT(SUM(B5:B14),SUM(C5:C14))
      The outer PRODUCT function takes the results of the two SUM functions and multiplies them together. In other words, it multiplies the sum of values in range B5:B14 by the sum of values in range C5:C14.
      When you AutoFill the above formula, it will automatically create relative cell references within a similar data range.
      If you want to get the total product result in a single cell, use absolute cell references instead of relative cell references in the above formula. Then, use the following formula instead of the one above
      =PRODUCT(SUM($B$5:$B$14),SUM($C$5:$C$14))
      Again, thank you for being with us.
      Regards
      Md. Abdur Rahim Rasel
      Exceldemy Team

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo