Product In Excel
Product excel function is an inbuilt mathematical function which is used to calculate the product or multiplication of the given number provided to this function as arguments, so for example, if we provide this formula arguments as 2 and 3 as =PRODUCT(2,3) then the result displayed is 6, this function multiplies all the arguments.
Product function in excel takes the arguments (input as numbers) and gives the product (multiplication) as an output. If cell A2 and A3 contains numbers, then we can multiply those numbers by use of PRODUCT in Excel.
PRODUCT Formula in Excel
=PRODUCT(number1, [number2], [number3], [number4],….)
Explanation
PRODUCT Formula in Excel has at least takes one argument and all other arguments are optional. Whenever we pass a single input number, it returns the value as 1*number, that is the number itself. PRODUCT in Excel is categorized as a Math/Trigonometric function. This PRODUCT Formula in Excel can take a maximum of up to 255 arguments in the later version after Excel 2003. In the Excel version 2003, the argument was limited up to only 30 arguments.
PRODUCT Formula in Excel not only takes the input number one by one as an argument but also it can take a range and can return the product. So, if we have a range of values with numbers and we want their product, we can do it either multiplying each one or directly using the PRODUCT Formula in Excel bypassing the range of the value.
In the above figure, we want to multiply all the values together given in range A1:A10, if we do it using the multiply (*) mathematical operator, it will take much time as compared to achieve the same using the PRODUCT function in excel since we will have to select each value and multiply, whereas using the product in excel we can pass the values directly as a range and it will give the output.
=PRODUCT(A1:A10)
Therefore, PRODUCT Formula in Excel =PRODUCT(A1:A10) is equivalent to the formula =A1*A2*A3*A4*A5*A6*A7*A8*A9*A10
However, the only difference is that when we use the PRODUCT function in excel and if we left the cell empty, PRODUCT in Excel takes the blank cell with value 1, but using the multiply operator, if we left the cell empty, Excel will take the value as 0 and the result would be 0.
When we deleted the cell value of A4, excel considers it as a 0, and returns the output 0, as shown above. But when we used the PRODUCT function in excel, it took the input range A1:A10, it seems that the PRODUCT in excel is ignoring the cell A4 which was empty, however, it doesn’t ignore the empty cell value but take the blank cell with value 1. It takes range A1:A10, and considers the A4 with value 1, and multiply the values of the cells together. It also ignores text values and logical values. The product in excel considers the dates and the numeric values as a number. Each argument can be supplied as a single value or cell reference or as an array of values or cells.
For small mathematical calculations, we can use the multiplication operator but in case we have to deal large data set where the multiplication of multiple values is involved then this PRODUCT function serves a great purpose.
So, the PRODUCT function in excel is beneficial when we need to multiply many numbers together given in a range.
Examples
Let’s look below at some of the examples of the PRODUCT function on Excel. These Excel PRODUCT Function examples will help you in exploring the use of PRODUCT function in Excel.
Example #1
Suppose, we have set of values, in column A and B that contains numeric values with some empty cells and we want to multiply each value of column A with column B, in such a manner, if any of the cells contain an empty value we get an empty value else returns the product of two values.
For example, B2 has an empty cell so, the result should be an empty value in cell C2.So, we will use the IF condition along with the OR function. If either of the cell value is nothing return nothing else returns the product of the numbers.
So, the PRODUCT Formula in Excel that we will use, is
=IF(OR(A2=””,B2=””),””,PRODUCT(A2,B2))
Applying, the PRODUCT Formula in Excel to each cell we have
Output:
Example #2 – Nesting of Product Function
When a PRODUCT in Excel is used inside another function as an argument, this is known as the Nesting of a PRODUCT function in Excel. We can use other functions and can pass them as an argument. For example, suppose we have four sets of data in Column A, B, C, and D. We want the product of sum value from the first dataset and second dataset with the sum of values from the third and fourth datasets.
So, we will use the SUM function and will pass it as an argument to the PRODUCT function in excel. We want the product of the sum of the value of Dataset A and Dataset B that is 3+3 multiplied with the sum of the value of Dataset C and C that is (5+2), so the result will be (3+3)*(5+2).
=PRODUCT(SUM(A2:B2),SUM(C2:D2))
In the above example, the sum function is passed as an argument to the PRODUCT function in Excel, this is known as nesting. We can even other functions also.
Example – #3
For example, suppose we have six divisions with a different number of persons employed for work. We have two tables with numbers of persons in each division and the work hour of each person in each division. We want to calculate the total work hour of each division.
So, we will be using the VLOOKUP function to lookup the values from both the tables and then we will pass it as an argument to get the total number by multiplying the number of person with the work hour per person.
So, the formula with Nested VLOOKUP will be,
=PRODUCT(VLOOKUP(G2,$A$2:$B$7,2,0),VLOOKUP(G2,$D$2:$E$7,2,0))
In this way we can do the nesting of function, depending upon the requirement and the problem.
Recommended Articles
This has been a guide to the PRODUCT Function in Excel. Here we discuss the PRODUCT Formula in excel and how to use the PRODUCT Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
