Using Excel's PRODUCT Function

How to multiply numbers and other types of data

In addition to a formula for multiplication, Excel also has the PRODUCT function, which can be used to multiply numbers and other types of data together.

Use the PRODUCT Function to Multiply Numbers, Arrays, or Ranges of Values

Multiplying numbers in Excel with the PRODUCT function

Ted French

In cells A1 to A3 of the example image, the numbers can be multiplied together using a formula containing the multiply (*) mathematical operator as shown in row 5, or the same operation can be carried out with the PRODUCT function as shown in row 6.

A product is the result of a multiplication operation no matter which method is used.

The PRODUCT function is most useful when multiplying the data in many cells. For example, in row 9 in the image, the formula =PRODUCT(A1:A3,B1:B3) is equivalent to the formula =A1 * A2 * A3 * B1 * B2 * B3. It's just easier to write.

Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the PRODUCT function is:

=PRODUCT( Number1, Number2, ... Number255 )

  • Number1 (required) is the first number or array that you want to multiply. This argument can be the actual numbers, cell references, or range of the location of data in the worksheet.
  • Number2, Number3 ... Number255 (optional) are additional numbers, arrays, or ranges up to a maximum of 255 arguments.

Data Types

Different types of data are treated differently by the PRODUCT function depending on whether they are entered directly as arguments in the function or as cell references to a location in the worksheet.

For example, numbers and dates are always read as numeric values by the function, no matter whether they are supplied directly to the function or are included using cell references,

As shown in rows 12 and 13 of the example image, Boolean values (TRUE or FALSE only), on the other hand, are read as numbers only if they are inserted directly into the function. If a cell reference to a Boolean value is entered as an argument, the PRODUCT function ignores it.

Text Data and Error Values

As with Boolean values, if a reference to text data is included as an argument, the function ignores the data in that cell and returns a result for other references or data.

If text data is entered directly into the function as an argument, as shown in row 11, the PRODUCT function returns the #VALUE! error value.

This error value is returned when any of the arguments that are supplied directly to the function cannot be interpreted as numeric values.

Note: If the word text is entered without quotation marks — a common mistake — the function returns the #NAME? error instead of #VALUE! All text entered directly into an Excel function must be surrounded by quotation marks.

Entering the PRODUCT Function

Options for entering the PRODUCT function and its arguments in cell B7 in the example image include:

  • Typing the complete function: =PRODUCT(A1:A3) into cell B7
  • Selecting the function and its arguments using the PRODUCT function dialog box

Although it is possible to enter the complete function manually, many people find it easier to use the dialog box because it takes care of entering the function's syntax, such as brackets and comma separators between arguments.

Opening the PRODUCT Dialog Box

To enter the PRODUCT function using the function's dialog box for the example image.

  1. Click on cell B7 to make it the active cell.
  2. Click on the Formulas tab of the ribbon.
  3. Click on PRODUCT in the list to open the function's dialog box.
  4. In the dialog box, click on the Number1 line.
  5. Highlight cells A1 to A3 in the worksheet to add this range to the dialog box.
  6. Click OK to complete the function and to close the dialog box.

The answer 750 appears in cell B7 because 5*10*15 is equal to 750.

When you click on cell B7, the complete function =PRODUCT(A1:A3) appears in the formula bar above the worksheet.