Product Excel Function

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Product In Excel

The PRODUCT Excel function is a built-in mathematical function used to calculate the product or multiplication of the given number provided to this function as arguments. For example, if we give the formula arguments 2 and 3 as =PRODUCT(2,3), the result is 6. This function multiplies all the arguments.

The PRODUCT function in Excel takes the arguments (input as numbers) and gives the product (multiplication) as an output. So, for example, if cells A2 and A3 contain numbers, then we can multiply those numbers using PRODUCT in Excel.

PRODUCT

PRODUCT Formula in Excel

=PRODUCT(number1, [number2], [number3], [number4],….)

PRODUCT Formula

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Explanation

The Excel PRODUCT formula has at least one argument. All other arguments are optional. Whenever we pass a single input number, it returns the value as 1*number, the number itself. The PRODUCT function in Excel is categorized as a Math/Trigonometric function. This PRODUCT formula in Excel can take up to 255 arguments in the later version after Excel 2003. In the Excel version 2003, the argument was limited to only 30 arguments.

The PRODUCT formula in Excel not only takes the input number one by one as an argument but also can take a range and return the product. So, if we have a range of values with numbers and want their product, we can do it by multiplying each one or directly using the PRODUCT formula in Excel, bypassing the value range.

PRODUCT Function 1

In the above figure, we want to multiply all the values in the range A1:A10. Suppose we use the multiply (*) mathematical operator, it will take much time compared to achieving the same using the PRODUCT function in Excel since we will have to select each value and multiply. Whereas using the PRODUCT function in Excel, we can pass the values directly as a range, and it will give the output.

=PRODUCT(A1:A10)

PRODUCT Function 2

Therefore, the 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 when we use the PRODUCT function in Excel. If we leave the cell empty, PRODUCT in Excel takes the blank cell with the value 1 but uses the multiply operator. Therefore, Excel will take the value as 0. So, the result would be 0.

PRODUCT Function 3

When we delete 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. The PRODUCT function in Excel seems to ignore cell A4, which was empty. However, it does not ignore the empty cell value but takes the blank cell with the value 1. It takes range A1:A10, considers the A4 with value 1, and multiplies the cells’ values. Moreover, it also ignores text values and logical values. The PRODUCT function in Excel considers the dates and numeric values as numbers. Each argument can be supplied as a single value, cell reference, or an array of values or cells.

For small mathematical calculations, we can use the multiplication operator. Still, if we have to deal with a 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.

PRODUCT Function 4

Examples

Let us look below are examples of the PRODUCT function in Excel. These Excel PRODUCT function examples will help you explore using the PRODUCT function in Excel.

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

Example #1

Suppose we have a set of values in columns A and B that contains numeric values with some empty cells. We want to multiply each value of column A with column B in such a manner that if any of the cells have an empty value, we get an empty value. Else, returns the product of two values.

PRODUCT Function example 1

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 values is nothing returned, 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:

PRODUCT Function example 2

Output:

PRODUCT Function example 3

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 columns A, B, C, and D. We want the product of the sum value from the first and second datasets with the sum of values from the third and fourth datasets.

PRODUCT Example 2

So, we will use the SUM function and 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 by the sum of the value of “Dataset C.” The“Dataset C,” value is (5+2), so the result will be (3+3)*(5+2).

=PRODUCT(SUM(A2:B2),SUM(C2:D2))

PRODUCT Example 2-1

In the above example, the sum function is passed as an argument to the PRODUCT function in Excel. It is known as nesting. But, of course, we can do 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 the 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.

PRODUCT Example 3

So, we will use the VLOOKUP function to lookup the valuesVLOOKUP Function To Lookup The ValuesThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more from both the tables and then pass it as an argument to get the total number by multiplying the number of people by 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))

PRODUCT Example 3-1

In this way, we can nest the function depending on the requirement and the problem.

This article is 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 examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Comments

  1. Rajib says

    nice thanks

Leave a Reply

Your email address will not be published. Required fields are marked *