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.

**Download the Practice Workbook**

**<< Go Back to Excel Functions | Learn Excel**

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

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 cellB5toB14. It adds up all the numbers in that specified range.SUM(C5:C14):calculates the sum of the values in the range from cellC5toC14. It adds up all the numbers in that specified range.=PRODUCT(SUM(B5:B14),SUM(C5:C14))The outer

PRODUCTfunction takes the results of the two SUM functions and multiplies them together. In other words, it multiplies the sum of values in rangeB5:B14by the sum of values in rangeC5: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 RaselExceldemy Team

Very Useful and helpful

Hello

George Jululian,You are most welcome.

Regards

ExcelDemy