PRODUCT Function in Excel (Table of Contents)
Introduction to PRODUCT Function in Excel
Being an Excel user, you might have fall for a situation sometimes, where you need to multiply two or more numbers. While excel has different ways to do so, there is one popular way among all and that is using PRODUCT function. Using this function you can do a product of (or Multiply) two or more than two numbers spread along with different cells of your Excel sheet. PRODUCT function in Excel is categorized under Math/Trigonometric function. This function takes at least one argument as an input and gives multiplication of the numbers as an output. If only, one argument is provided then it multiplies that number with 1 and gives output as a product. It is helpful when you have data scattered along with the ranges.
Syntax for PRODUCT Function:
Arguments of PRODUCT Function:
number1 – Is the required/mandatory argument which specifies the number to be multiplied.
number2 – [optional] The second number or range to multiply.
Examples of PRODUCT Function
Let’s look at some examples of Excel PRODUCT function and see how to use the PRODUCT function in Microsoft Excel.
Example #1 – Multiplication of Three Numbers Using PRODUCT Function
Suppose we have numbers to be multiplied in column A of the current working sheet as shown below:
All we need is a product (multiplication) of these three numbers under cell B2 of the current working sheet.
Step 1: Start typing the PRODUCT formula in cell B2 of the current working sheet.
You can alternatively add this formula using Formulas tab present in the uppermost ribbon of your excel sheet.
Go to “Formulas” > “Math & Trig” > “PRODUCT” > Enter.
Step 2: Provide an argument for PRODUCT function. In this case, you want the product/multiplication of numbers present in cell A2, A3, and A4. Therefore give A2:A4 as an argument under PRODUCT function.
Step 3: Complete the formula by entering closing parenthesis and press the Enter key. You’ll see the multiplication of three numbers as an output under cell B2.
Example #2 – Difference Between PRODUCT and General Multiplication
In this example, we are going to see how the PRODUCT function is more versatile than the general multiplication we do using an asterisk (“*”). Suppose we have data as shown in the screenshot below:
We can see that there are some blank cells between the numbers in column A.
Step 1: In cell B2, try to do multiplication of all the cells with each other (including blank cells) with the help of multiplication operator a.k.a. asterisk (“*”).
Step 2: Press the Enter Key to see the output. You’ll get Zero under cell B2 of the current working sheet. See the below screenshot for your reference.
This is because if we left the cell empty, the multiplication operator considers the value as zero for that cell and multiplies it with other numbers which ultimately gives zero as output.
Step 3: Now use PRODUCT function over the same range of data (A2:A11). Use the following formula under cell B3.
Step 4: Press the Enter key to see the output of this formula under cell B3. You’ll see an output as shown below.
This is because PRODUCT function does ignore the cells with blanks, texts or non-numeric data and gives product/multiplication of those cells which has numeric data in them.
Example #3 – PRODUCT Function in Combination with SUM Function
We also can use PRODUCT function in combination with different mathematical/trigonometric functions. Let’s see how the SUM function can be used within PRODUCT function. Suppose we are having two offices across the city and employee are working on both offices altogether with some work from home facility available for them.
We wanted to check the product of hours an employee worked under office A & B (Sum of Office & Work From Home Hours). This can be done using the combination of function SUM and PRODUCT. See the data below:
Step 1: In cell E2 of the active worksheet, start typing formula for PRODUCT function which is =PRODUCT.
Step 2: Now, use SUM(A2, B2) as a first argument under PRODUCT function and SUM(C2, D2) as a second argument under PRODUCT function.
After using the PRODUCT Formula output is shown below.
Step 3: Close the parentheses and press Enter key to see the output of this formula. Also, drag the formula across the different rows to get the final output as below.
This might not look as a conventional way to capture the final hours. But this example is created to just make you understand how the SUM can PRODUCT function can be used in combination.
Example #4 – PRODUCT Function Inside Some Other Function
We can also use the PRODUCT function inside any other function. Suppose we have data as shown in the table below and all we wanted is to make the sum of the product of A2, B2, and product of C2, D2.
Step 1: In cell E2 of Active worksheet, start typing formula for SUM.
Step 2: Now, use PRODUCT(A2, B2) as a first argument and PRODUCT(C2, D2) as the second argument inside SUM function as shown below.
Step 3: Close the parentheses and press Enter key to see the output under cell E2. You’ll see an output as shown below.
Here, first the product of A2, B2 is 12 and that of C2, D2 is 32. Thus, 12 and 32 would be the two arguments under SUM function. At the end, SUM function adds both of them and gives output as 44.
Drag the formula across the cells to see the output for all working cells as below.
This is from this article. Let’s wrap the things up with some points to be remembered:
Things to Remember About PRODUCT Function in Excel
- PRODUCT function accepts maximum of 255 arguments at a time.
- The numbers can be added separately or can be stored under cells and used those cells as a reference to the PRODUCT function.
- If the cells are left blank, stored with numbers as text, logical texts, etc., the PRODUCT function ignores such cells while performing calculations
- If the cells are consisting of any other text values such as a name of a person, PRODUCT function gives #VALUE! error.
- Product function only takes numeric arguments and work on those. If the cells given as arguments to PRODUCT function are containing either text, blank cells are ignored by this formula. Which can be considered as an advantage of it over the conventional multiplication.
Recommended Articles
This is a guide to PRODUCT Function in Excel. Here we discuss How to use PRODUCT Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
