How to Use the PRODUCT Function in Excel

This guide will explain how to use the PRODUCT function in Excel.

When performing some calculations, you may need to multiply a series of numbers together. For example, an inventory tracker where multiplying the quantity of the unit price and other factors is needed. 

The PRODUCT function in Microsoft Excel allows you to calculate the product of two or more numbers. This function finds the product of a range of values without needing to use the multiplication operator (*) several times in a row.

This guide will provide a step-by-step tutorial on how to use the PRODUCT function to calculate the product of multiple arguments. We’ll also show you how the PRODUCT function can help handle missing or invalid data within a range.

The Anatomy of the PRODUCT Function

The syntax of the PRODUCT function is as follows:

=PRODUCT(number1, [number2], ...)

Let’s look at each argument to understand how to use the PRODUCT function.

• = the equal sign is how we start any function in Excel.
• PRODUCT() refers to the built-in PRODUCT function in Excel. The function multiplies all numbers given as arguments and returns a single product.
• number1  is the first number or range you wish to multiply.
• [number2], … are the additional numbers or ranges you want to multiply.
• Excel allows up to a maximum number of 255 arguments.
• Do note that only numbers in the given array or reference are multiplied. Cells with logical values and text in the array or reference are ignored. Empty cells will also be ignored.

A Real Example of Using the PRODUCT Function in Excel

Let’s explore a few examples where we can use the PRODUCT function to multiply a range of numerical values.

Using PRODUCT to Multiply a Range of Cells

In the table above, we have a list of values that we want to multiply together.

We can use the following formula in cell C2 to find the product of the values in cells A2:A6:

=PRODUCT(A2:A6)

Using the PRODUCT function, we discover that the product of all numbers in the target range is -3750.

PRODUCT Function vs. Multiplication Operator.

If we were to use just the multiplication operator to multiply a range of values, we’d need to construct a lengthy formula.

For example, to multiply the range A2:A10 we can use the following formula:

=PRODUCT(A2:A10)

This formula is much more concise than using the following formula with the multiplication operator:

=A2*A3*A4*A5*A6*A7*A8*A9*A10

Aside from convenience, the PRODUCT function also automatically ignores non-numerical data from arguments. This can be useful if your data contains empty cells or may contain text data that you would prefer to skip over.

In the example above, our PRODUCT function ignores cells A4 and A7 since they are either empty cells or cells with text data.

If we were to use the multiplication operator, we would encounter an error or inaccurate result.

Using the PRODUCT Function with Multiple Ranges

The PRODUCT function also accepts multiple ranges as arguments. For example, you may have values found in two separate ranges that you want to multiply together.

In the example above, we used the formula =PRODUCT(A2:A5,C2:C5) to multiply numbers found in the cell ranges A2:A5 and C2:C5.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the PRODUCT function in Excel.

How to Use the PRODUCT Function in Excel

1. Select an empty cell to output the result of the PRODUCT function.
   
   In this example, we’ll use the PRODUCT function in cell C2 to output the product of the range A2:A6.
   
2. Type “=PRODUCT(“ to begin the PRODUCT function in the selected cell.
   
   Next, type the first cell or range containing numbers you wish to multiply. For this example, we’ll find the product of a single range (A2:A6).
   
3. You can choose to add another cell or cell range by entering it as another argument.
   
4. After entering all the desired cells or ranges, use the Enter key to evaluate the formula.
   
   In the example above, we’ve determined that the product of our target range is -225.

These are all the steps you need to know to start using the PRODUCT function in Microsoft Excel.

FAQs

1. Can I use the PRODUCT function to multiply an entire column?
   Yes. You can use the PRODUCT function to multiply all values in a column in your Excel spreadsheet. For example, to multiply all values in column B we can use the formula =PRODUCT(B:B).

To learn more about using Excel to multiply a range of values, you can read our post on how to calculate a dot product in Excel. 

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!