# 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 situations, especially when you need to work with lots of numbers.

The screenshot below is an overview of one of the applications of the PRODUCT function we will discuss in this article, and the dataset we’ll use to illustrate them.

## PRODUCT Function in Excel: Syntax

• Function Objective:

The PRODUCT function is used to calculate multiplication of numbers in Excel.

• Syntax:

=PRODUCT(number1, [number2], …)

• Arguments Explanation:
Argument Required/Optional Explanation
number1 Required The first number of the range of numbers to be multiplied.
number2 Optional Extra numbers or range of numbers to be multiplied.
• Return Parameter:

Multiplied value of the given numbers within the argument field.

## Example 1 – Using the PRODUCT Function with Numerical Values in Excel

We can use the PRODUCT function to multiply values in the traditional way, namely by putting the product sign (*) in between the numbers to be multiplied, for instance 5*8.

Steps:

• In cell E5, enter the following formula:
`=PRODUCT(4*4*7)`
• Press ENTER.

The end result is as in the picture below:

## Example 2 – Using the PRODUCT Function with Cell References

To multiply values stored within cells, we specify the cell references separated by a comma inside the PRODUCT function argument field.

Steps:

• In cell E5, enter the following formula:
`=PRODUCT(B5,C5,D5)`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The end result is as in the picture below:

## Example 3 – Using the PRODUCT Function with Numbers in Different Ranges

We can multiply multiple series of numbers, separated by a comma.

Steps:

• In cell E5, enter the following formula:
`=PRODUCT(B5:B11,C5:C11)`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The end result is as in the picture below.

## Example 4 – Combining SUM and PRODUCT Functions to Multiply Two or More Sums

Let’s multiply the sums of some ranges of numbers.

Steps:

• In cell E5, enter the formula within the cell:
`=PRODUCT(SUM(B5:B14),SUM(C5:C14))`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The end result is as in the picture below:

## Example 5 – Finding the Square Root of a Product with SQRT and PRODUCT Functions

Steps:

• In cell E5, enter the following formula:
`=SQRT(PRODUCT(B5:D5))`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The end result is as in the picture below:

## Example 6 – Using the PRODUCT Function with Ranges That Have Empty Cells

The PRODUCT function will ignore the blank cells within the specified range.

Steps:

• In cell E5, enter the following formula:
`=PRODUCT(B5:D5)`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The end result is as in the picture below:

## Example 7 – Using the PRODUCT Function with Text Values

The PRODUCT function will also ignore the cells containing text within the specified range, only counting cells containing numerical values.

Steps:

• In cell E5, enter the following formula:
`=PRODUCT(B5:D5)`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The end result is as in the picture below:

## Example 8 – Using the PRODUCT Function to Multiply Data Based on a Condition

If we find a blank cell in a row, we want to exclude it from the calculation of the product of that row.

Steps:

• In cell E5, enter the following formula:
`=IF(OR(C5="",D5=""),"",PRODUCT(B5:D5))`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The formula will return a blank cell if either of the values to be multiplied are blank, else it will return their product.

The end result as in the picture below:

## Example 9 – Using the PRODUCT Function to Multiply Two or More Outputs of the VLOOKUP Function

We can use the PRODUCT function along with the VLOOKUP Function.

Steps:

• In cell E5, enter the following formula:
`=PRODUCT(VLOOKUP(H5,\$B\$5:\$C\$12,2,0), VLOOKUP(H5,\$E\$5:\$F\$12,2,0))`
• Press ENTER.
• Drag the Fill Handle icon to the end of the Product column.

The end result is 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.
• If all the reference cells contain text, the PRODUCT function will return a #VALUE error.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

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

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