WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Product Excel Function

Product Excel Function

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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 contain numbers, then we can multiply those numbers by use of PRODUCT in Excel.

PRODUCT

PRODUCT Formula in Excel

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

PRODUCT Formula

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.

PRODUCT Function 1

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)

PRODUCT Function 2

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
Sale
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

PRODUCT Function 3

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 takes 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.

PRODUCT Function 4

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 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 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.

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

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 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.

PRODUCT Example 2

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))

PRODUCT Example 2-1

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 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 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))

PRODUCT Example 3-1

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 –

  • PRODUCT Formula
  • RIGHT Function in Excel | Examples
  • IFERROR Function
  • POWER Function in Excel
  • XOR in Excel
1 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download PRODUCT Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More