PRODUCT in Excel (Formula, Examples) | How to Use PRODUCT Function

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 function 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 (Table of Contents)

Product In Excel
PRODUCT Formula in Excel
Explanation of PRODUCT Function in Excel
How to Use PRODUCT in Excel With Examples

Product In Excel

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 of PRODUCT Function in Excel

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 maximum up to 255 arguments in the later version after Excel 2003. In 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.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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. 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 calculation we can use the multiplication operator but in case we have to deal large data set where multiplication of multiple values are 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.

How to Use PRODUCT in Excel With Examples

Let’s look below at some of the examples of PRODUCT function on Excel. These Excel PRODUCT Function examples will help you in exploring the use of PRODUCT function in Excel.

You can download this PRODUCT Function Excel Template here – PRODUCT Function Excel Template

PRODUCT 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:

PRODUCT in Excel 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 first dataset and second dataset with the sum of values from third and fourth dataset.

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 value of Dataset A and Dataset B that is 3+3 multiplied with the sum of 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.

PRODUCT in Excel 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.