While there is no “Excel multiply formula” there are multiple ways to multiply in Excel. For instance, do you use an asterisk (*) to multiply, but hit a brick wall when you apply other arithmetic operators? What about shortcuts for multiplying many numbers in one step?
Read on for three powerful ways to perform an Excel multiply formula.
1. Multiplication with *
To write a formula that multiplies two numbers, use the asterisk (*). To multiply 2 times 8, for example, type “=2*8”.
Use the same format to multiply the numbers in two cells: “=A1*A2” multiplies the values in cells A1 and A2.
You can mix and match the * with other arithmetic operators, such as addition (+), subtraction (-), division (/), and exponentiation (^). In these cases, remember that Excel carries out the operations in the order of PEMDAS: parentheses first, followed by exponents, multiplication, division, addition, and subtraction.
In the following formula “=2*3+5*6,” Excel performs the two multiplication operations first, obtaining 6+30, and add the products to reach 36.
What if you want to add 3+5 before performing the multiplication? Use parentheses. Excel will always evaluate anything in parentheses before resuming the remaining calculations following PEMDAS. In the case of “=3*(3+5)*6”, Excel adds 3 and 5 first, resulting in 8. Then it multiplies 3*8*6 and reaches 144.
If you have trouble remembering the order of PEMDAS, use the Aunt Sally mnemonic device: use the first letters of the sentence, “Please Excuse My Dear Aunt Sally.”
2. Multiplication with the PRODUCT Function
When you need to multiply several numbers, you might appreciate the shortcut formula PRODUCT, which multiplies all of the numbers that you include in the parentheses.
The arguments can be:
- Numbers or formulas separated by commas, such as:
=PRODUCT(3,5+2,8,3.14)
This is equivalent to =3*(5+2)*8*3.14.
- Cell references separated by commas, such as:
=PRODUCT(A3,C3,D3,F3).
This is equivalent to =A3*C3*D3*F3.
- A range of cells containing numbers, or multiple ranges separated by commas, such as:
=PRODUCT(F3:F25),
which is equivalent to =F3*F4*F5*(and so on, all the way up to)*F25, or:
=PRODUCT(F3:F25,H3:H25).
- Any combination of numbers, formulas, cell references, and range references.
In each case, Excel multiplies all the numbers to find the product. If a cell in the range is empty or contains text, Excel leaves that cell’s value out of the calculation. If a cell in the range is zero, the product will be zero.
3. Multiplication of Ranges with the SUMPRODUCT Function
Consider the following invoice. The formula in Column E (with the formula shown to the right of the table) multiplies quantity by the price each to reach an extended price. The total in cell E7 sums up the extended prices.
But what if you don’t want the extended prices to show as separate calculations? What if you want to do it all in one step?
Try the SUMPRODUCT function, which multiplies the cells in two ranges and sums the results.
SUMPRODUCT(D2:D5,C2:C5) multiplies D2*C2, D3*C3, etc., and sums the results. Note that the result, 84.50, is the same as the previous example.
This function is invaluable for calculating weighted averages, such as classroom grades or prices based on variable state tax, in which you multiply a range of values by a range containing the weights.
Next Steps
These are but three of the methods to multiply numbers in Excel formulas. When you’ve mastered them, try the PRODUCTIF formula, which multiplies all numbers in a range if a condition is met.
Until then, try mixing and matching the multiplication formulas, using any combination along with other arithmetic functions, to create complex mathematical models.
8 Comments
Hi,
I want to calculate sum of the input entered in input data cell having data like:
1. Input Data in the cell is delimited by any character like / , , ..
2. Input data cell I am using here is : 1/2/3/4
3. In output data cell I want final result
4. In output data cell result should be calculated like:
multiply each digit of input data cell delimited by /, with any constant and add them.
Parsing data (separating it into useful components) can be a bit involved but Excel is quite capable of it. The critical functions in use are FIND() and MID(). (The exact uses of these functions are in the red boxes). One FIND() per “/” and one MID() per number. The data you described is on the left the parsing, in sections on the right (A2 is the focus for most of it).
Columns D, E and F find the exact positions of the “/” in the original data (positions 2, 4 and 6). Columns H, I and J then use the position data to pull apart the 4 fields into 1, 2, 3 and 4.
Then the Constant is multiplied by each, on row 8.
If the length of your data varies, you can still apply these techniques to the longest data in the set and apply that to every row. This will still produce useful results on shorter data.
is there an easier formula for this?
=(F3*I3)+(F4*I4)+(F6*I6)+(F7*I7)+(F8*I8+(F9*I9)……..
One solution would be to multiply in a separate column and then add it all up. For example, if your J column is blank, in J3 you can type =F3*I3. Then you can autofill down for as many calculations as you need. This keeps you from having to type out (F4*I4), (F6*I6), etc. You can do this for as many lines as you need. Let’s say you stop at row 20. In J21 you can type =SUM(J3:J20) to get your answer.
Hi I want an equation in which if the number i put into the cell is within 1-3 i want it to multiply by 6.5 in the cell next to it but if the numbers are from 4-10 i want them to multiply by 5 and so on
It appears that what you are talking about is a nested “IF” function.
If the value were in cell A1, the answer would be in B1 and would read: =IF(A1<4,A1*6.5,IF(AND(A1>4,A1<11),A1*5,-99))
This is one IF within another. There’s also an AND() function which tests whether or not, two conditions are true at once.
HI.
Is there any ways that excel can recognize x as a multiple*?
For example 5 x 6 = 5*6?
Excel’s multiplication character “*” is actually programming code.
Most modern programming languages (including the ones used to run Excel) apply the asterisk as a multiplication command.
If you’re needing to copy in text (that already has “X” as the multiplier in it) you might try a find/replace to substitute * for X in the copy text.
It’s not currently possible (or recommended) to alter the math operators in Excel.