Product formula in excel is an inbuilt formula for multiplication in excel, it can take n number of arguments and it multiplies each argument provided to it, the method to use this formula is =PRODUCT( number 1, number 2,….., number n), it is a simple multiplication function where instead of using * or asterisk operator for multiplication we use this function.
Product Formula in Excel (Table of Contents)
Excel PRODUCT Formula
The PRODUCT() functions multiply all the numbers in the range and return the value of it. In excel it comes under Math/Trig functions.
In the latest Excel versions (post-2007) we can input up-to 255 arguments. The earlier version has restricted number of entries up-to only 30. Each input may consist of a number, single cell reference, Cell range also. It means that the number1, number 2, etc can be as below:
number1 -> 3(number), A2(single cell reference), Range(A1:A5)
Hence, we can have a multiplication of 255 numbers, or 255 different cell values or 255 different cell ranges.
How to use PRODUCT() Formula in Excel?
Let’s look below at some of the examples of PRODUCT formula in Excel.
Excel Product Formula – Example #1
We will start with the first simple example to use excel PRODUCT formula.
Below is the data for reference:
There are 3 ways in which we can specify the input to multiply these numbers and will see in the below explanation:
1) The first method is input numbers by typing them inside the product formula in excel one by one.
2) Type “=PRODUCT(num1, num2, num3,…)”. Here num1, num2, num3 are numbered in the data table. So, type “=PRODUCT(“ as shown below:
3) After this step, input each number one by one with coma in between two inputs. “=PRODUCT(3,5,6,7,8)” as shown below:
4) Press ENTER and you will the final output as the value of the multiplication of these numbers as shown below in the screenshot:
5) Using single cell references. Here instead of using manual input of number, we shall select cells containing these numbers one by one.
6) Type “=PRODUCT(A2, A3, A4, A5, A6)”. Individually selecting each cell and inserting coma in between each cell reference input. And in excel format, it’s shown below:
7) Press ENTER and you will see the value same as an earlier step. Refer screenshot below:
8) The last method is selecting a cell range. In this method, we select a range by dragging from the first cell to the last cell of the table.
9) Type “=PRODUCT(drag from cell A2 to A6)”. Refer screenshot below as shown:
As we can see, instead of input of numbers individually or cell references, in just one cell selection range as one input/argument, we get the same value of the product.
Excel Product Formula – Example #2
Suppose we need to multiply two sets of numbers and one of which contains blank cells i.e no values in the cell to multiply as shown in the data below:
1) We will use the cell range method for the above table.
2) Type “PRODUCT(A2:B2). Here we are giving a range of two columns as shown in the screenshot below:
3) Press ENTER and you will see the value returned as shown below:
4) Drag the formula for all the cells and you will see the value for each multiplication of the numbers as shown below:
5) If you carefully observe the blank cell multiplication value(highlighted in red arrow), we see that the return value is the same as that in set 1 i.e. the cell which is not blank.
6) This means that blank cells are taken as “1” in product function calculation i.e. it multiplies “3” with “1”, hence the output is “3” and its multiplying “3” with zero else value would be zero
Excel Product Formula – Example #3
Suppose we have a set of data containing the number of items purchased and its price per piece. We would like to know the total value or cost of items purchased. Here we will use operation as SUMPRODUCT()
SUMPRODUCT() performs a similar function as PRODUCT(), the only difference is that it sums up at the end all the multiplication done. Below is the set of data we need to work on:
Rather than multiplying each entity with each other and lastly calculate the total, we will use SUMPRODUCT()
Notice that we have to input an array i.e. cell ranges in the arguments. In simple terms, each value in an array will correspondingly multiply with each value in other arrays, mostly the adjacent one.
Below are the steps on how to use SUMPRODUCT():
1) We have to take cell range from A2 to A6 as one array and B2 to B6 as another array.
2) Type “=PRODUCT(select array1)” as shown below:
3) Then type coma(,) and insert by selecting array2 as shown below:
Press ENTER and we will return value of it. However, let’s verify how it works
4) As shown below, the value is 1590. Now, we will check using standard PRODUCT() formula method.
5) Type “=PRODUCT(cell range)”. Here the range is A2 to B2 as shown below:
6) Similarly, drag or insert the same formula for the rest of the data points in the table and the corresponding value is as shown below:
7) Now, calculate the sum of cell values in column F using SUM() function and below is the value as shown:
8) It means that SUMPRODUCTS() first multiplies each cell in array1 with each adjacent cell in array 2. Once all cells are multiplied it gives back the total of all the multiplications and the final output is the same as the product formula of each cell
Excel Product Formula – Example #4
Let’s take an example where we need to use another function as an argument or input in the PRODUCT() function.
Assume we have a set of data, wherein the purchase of items from the store on Day 1 and Day 2 but in different quantities and each item has the price as shown below:
Here now we have items purchased on Day1 and Day2, we will approach this as first summing up the number of items in both the columns and the next step would be to multiply with the price of each item. Below are the steps:
1) Type “=PRODUCT( sum range of A3 to B3” as shown below:
2) Type coma(,) and then select cell reference of the “Price” as shown below:
3) Press ENTER and final value would be (2+1) multiply by 50 which is 150. Drag and drop the formula for rest of the other cells as shown below:
Here, we shall take one small example for further explanation:
Things to Remember
- If you try to multiply a character it returns value as first cell numeric value present i.e. it takes the character as “1” and multiplies it with other as shown below:
This has been a guide to Product Formula in Excel. Here we will learn how to use Product() formula in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –