How to calculate the product in MS Excel

In Microsoft Excel, the PRODUCT function calculates the product of all numbers in a given range or array. It’s commonly used to find the total multiplication result of a series of values, making it handy for various financial, engineering, and statistical calculations within spreadsheets. Simply input the range or array of numbers as arguments, and Excel swiftly computes the product.

Syntax

The basic syntax for the PRODUCT function is as follows:

Here, number1 and number2 represents the cell number or any numeric value. It’s worth noting that we have the flexibility to include additional cells as needed.

Note: Ranges or cell references may be used instead of explicit values.

Simple values

If we simply want to find out the product between 10, 20, and 30 we will use the PRODUCT function using the formula below:

List of cells

If we want to find the product by providing the list of cells, we can use the formula below:

Range of cells

If we want to find the product by providing the range of cells, we can use the formula below:

If we want to use the range of cells, and skip a cell number in that case we can use the formula below:

The above formula calculates product of cells A1, A2, A3, and A5.

Steps

The following is a step-by-step guide to using the PRODUCT function:

1. Select the cell: We click the cell where we want to calculate the product.

2. Use the PRODUCT function: We type =PRODUCT( into the selected cell.

3. Select the range: If we want to calculate the product of the number of cells in the range A1 to A5, there are two ways to achieve the desired result:

   1. We can enter “A1:A5” after the opening parenthesis and then close the parenthesis and press “Enter.” The formula should look something like this: =PRODUCT(A1:A5).

   2. We can also enter A1,A2,A3,A4,A5 after the opening parenthesis and then close the parenthesis and press “Enter.” The formula should look something like this: =PRODUCT(A1,A2,A3,A4,A5).

Let's now apply the steps above on our dataset. Let's suppose we have the following dataset:

Now, our objective is to determine the product of the marks a student receives in the provided dataset. We have discussed two methods to calculate the product. Let’s use both methods:

Using the PRODUCT function with list of cells

First, we select the cell, where we need to calculate the product. Let's suppose here the cell number is B9. After we select the “cell(B9),” write the following in the cell:

Let's visualize the steps above in action.

Note: 

• As we can see in the slides above, when multiple cells are added, the system automatically highlights the cell number and alters its color for easier identification.

• If an empty cell is included in a product formula and subsequently populated with a value, the value in “cell B9,” where the product is calculated, will automatically update to reflect the new value.

Using the PRODUCT function by specifying the range

First we select the cell, where we need to calculate the product. Let's suppose here the cell number is B9. After we select the “cell(B9),” write the following in the cell:

Let's visualize the steps above in action.

Things to remember

• If we try to find the product of any value (other than numbers) enclosed in double quotes, it will throw a #VALUE! error.

• Empty cells, text, the logical values TRUE and FALSE, and text values are all ignored when used with a range or by typing the cell numbers.