What is PRODUCT Function in Google Sheets?
The PRODUCT function in Google Sheets is used to multiply numbers together. It returns the product of these numbers. You can either multiply the numbers directly or specify their references. It is very helpful in scenarios where we may have to multiply products and their prices without the tedious use of calculations with the *. If you want to multiply the values in cells A1 and A2, you use:
=PRODUCT(A1, A2).
For example, to multiply 3 numbers in cells A1 to A3, you can either supply them directly like
=PRODUCT(2, 3, 5) or use a range like
=PRODUCT(A1:A3).
This would return 30. This function can be used with up to 30 arguments. If any of the values are zero, as known to you, the result will be a zero.
Key Takeaways
- The PRODUCT function in Google Sheets multiplies the numbers provided as arguments and returns the result.
- The input to the function can be direct numbers, a range, or cell references of numbers.
- The syntax of the PRODUCT function is as follows:
=PRODUCT(value1, [value2, …])
value1, value2, …: These are the numbers or ranges that you want to multiply. You can have up to 30 arguments in total.
- If any argument is 0, the result will be 0 as a number multiplied by 0 is always 0. If an argument is empty, it is treated as one.
Syntax
The PRODUCT function multiplies all the numbers specified. It is a pretty straightforward function increasing the efficiency of your calculations. The syntax of PRODUT is simple as well.
=PRODUCT(value1, [value2, …])
Here, value1, value2, …: These are the arguments. These could be numbers or cell references that you want to multiply. You can input up to 30 arguments, and they can be individual numbers, cell references, or ranges of cells.
How to Use PRODUCT Function in Google Sheets?
The PRODUCT function can be manually entered as a formula in Google Sheets. It can also be entered through the Google Menu bar. First, let us look at how we can enter the function manually.
Step 1: We have some numbers in a few cells. Select the cell where you want the result of the multiplication to appear. Here, we choose cell C2.
Type the following formula in the selected cell:
=PRODUCT(
Step 2: Select the numbers or range to multiply. You can also manually enter the numbers separating them with commas. Here, we enter the range. Close the parentheses.
=PRODUCT(A1:A6)
If you want to multiply the values in any cells A1, A2, and A3, we type:
=PRODUCT(A1, A2, A3)
Step 3: Press Enter on your keyboard. The cell will display the product of the range you selected.
Entering the DURATION through the Menu Bar.
- Place the cursor where you want the formula to be entered.
- Go to the Insert tab in Google Sheets.
- Select the option Function and then Math.
- From the list of functions, select the DURATION function.
Examples
Using the function PRODUCT is very simple! In the examples below, let us see how to apply the function in real-world scenarios.
Example #1
In this PRODUCT function in Google Sheets example, let us take a store which has several items. Here, we must calculate the total cost of the items a customer is buying according to the different quantities and prices. Let us look at the steps below.
Step 1: In Google Sheets, create a table as shown in the image below.
- Here, the Quantity column represents the number of items the customer buys.
- Price per Unit represents is the price of each item.
- The Total Price column will calculate the total cost for each item.
Step 2: Now, let us enter the PRODUCT Function. Click on cell D2.
Enter the following formula:
=PRODUCT(B2, C2).
Here, we multiply the quantity in cell B2 by its price in cell C2.
Press Enter.
Step 3: Drag it down to fill the formula for the other items.
Step 4: You can find the total bill cost for this customer by using this simple formula in cell D7.
=SUM(D2:D6).
So, the total cost for all items is $264.
Example #2 – Multiplying Multiple Ranges
To multiply multiple ranges with the PRODUCT function, we just use the basic PRODUCT function to handle multiple ranges. It can also handle a combination of individual values and ranges. Let us look at these simple examples.
We again take a scenario where we have two columns of numbers. We will find their product as followed. Suppose you have numbers in cells A1 through A4 and B1 through B4 and you want to multiply all of them together.
Step 1: Set up your data by adding the details in a table as shown below. We have multiple ranges, and we will multiply them to find the total product.
Step 2: Enter the following formula in Cell C2.
=PRODUCT(A1:A4, B1:B4)
The PRODUCT function will multiply all values in both ranges and return the total value.
Step 3: Press Enter. The result should be as follows: (2*1) * (4*3) * (6*5)*(8*7) = 9000
Example #3 – Using PRODUCT with FILTER Function
In this interesting example, we have some items in a bakery, the quantity purchases and the price per unit. Here, we select all the cake items which have been bought and multiply the number bought and cost per unit using the FILTER function. GO ahead and read on!
Step 1: Enter all the details as shown below.
Step 2: Let us extract all the number of cakes bought by using the following FILTER formula.
=FILTER(B2:B5, A2:A5=”Cake”).
This FILTER function first extracts all the units purchased where the item in Column A is “Cake.”
We can similarly use the function shown below to extract the cost per unit of cake.
=FILTER(C2:C5, A2:A5=”Cake”).
Check the outcome of these two functions in columns E and F, respectively.
Step 3: Now, we wish to use the PRODUCT and FILTER function. To multiply all these numbers together, enter the following formula in cell E5
=PRODUCT(FILTER(B2:B10, A2:A10=”Cake”), FILTER(C2:C10, A2:A10=”Cake”))
Press Enter.
You get the result as 200, which is the product of all four numbers.
Important Things to Note
- The PRODUCT function can multiply numbers that are the output of other functions or formulas.
- If any of the values is 0, the result will be 0 as any number multiplied by zero is zero.
- The PRODUCT function in Google Sheets can handle large numbers of values; you can give up to 30 arguments.
Frequently Asked Questions (FAQs)
While the PRODUCT function is pretty straightforward, there are some errors you might encounter when using PRODUCT. Some of them include:
• The #VALUE! Error occurs if one of the cells in the range contains text or special characters instead of numeric values. Hence, always ensure your dataset contains numbers.
• #DIV/0! Error happens if we use division within a nested operation using PRODUCT. Always ensure no division by zero is included.
When using the PRODUCT in Google Sheets, we can give the following inputs.
One of the most common uses is multiplying the values in a single range. Let us look at how the formula is written:
=PRODUCT(B1:B5).
It multiplies all the values in Column B from B1 to cell B5.
If we must multiply numbers from multiple, non-contiguous ranges, we can specify those ranges and cells using the PRODUCT function as shown in the example below.
=PRODUCT(C1:C3, E1:E3, D1).
By combining ranges, we can handle complex datasets without any difficulty in multiplying.
Besides individual and multiple ranges, the PRODUCT function can also include individual numbers within its arguments. In this example, we multiply the values in cells A1 to A5 by 5.
=PRODUCT(A1:A5, 5).
One of the most important uses is to calculate the total sales by multiplying the quantity sold by the unit price.
Another use is to utilize the PRODUCT function to calculate the total cost of an item after a discount.
Download Template
This article must help understand PRODUCT Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is PRODUCT Function in Google Sheets. We learn how to use it to find the product of numbers with examples & working template. You can learn more from the following articles. –
Leave a Reply