Multiply Numbers with Excel's PRODUCT Function

of 01

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

Multiplying numbers in Excel with the PRODUCT function
Multiplying Numbers in Excel with the PRODUCT Function. (Ted French)

As well as using a formula for multiplication, Excel also has a function—the PRODUCT function—that can be used to multiply numbers and other types of data together.

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

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

The PRODUCT function is probably most useful when multiplying together 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 * C1 * C2 * C3. It's just easier and quicker 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) the first number or array that you want to multiply together. This argument can be the actual numbers, cell references, or range to the location of data in the worksheet.

Number2, Number3...Number255 - (optional) 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 it is entered directly as an argument into the function or whether a cell reference to its location in the worksheet is used instead.

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 whether they are included using cell references,

As shown in rows 12 and 13 in the image above, 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 just ignores the data in that cell and returns a result for other references and/or data.

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

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

Note: If the word text was entered without quotation marks—a common mistake—the function will return the #NAME? error instead of #VALUE!

All text entered directly into an Excel function must be surrounded by quotation marks.

Multiplying Numbers Example

The steps below cover how to enter the PRODUCT function located in cell B7 in the image above.

Entering the PRODUCT Function

Options for entering the function and its arguments include:

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

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

The steps below cover entering the PRODUCT function using the function's dialog box.

Opening the PRODUCT Dialog Box

  1. Click on cell 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;
  7. The answer 750 should appear in cell B7 since 5*10*15 is equal to 750;
  8. When you click on cell B7 the complete function =PRODUCT(A1:A3) appears in the formula bar above the worksheet.