3 Ways to Multiply in Excel

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.

chart2

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.

New image for fig 1

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.

New image for fig 2

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.

Local Seminars Related to this Topic:

Related Formulas Articles

Categories

Monthly Archives

8 Comments

  • Shikher Gupta says:

    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.

    • Excel Tips and Tricks from Pryor.com says:

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

  • Sherry says:

    is there an easier formula for this?

    =(F3*I3)+(F4*I4)+(F6*I6)+(F7*I7)+(F8*I8+(F9*I9)……..

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Sam says:

    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

    • Excel Tips and Tricks from Pryor.com says:

      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.

  • Sunshine says:

    HI.

    Is there any ways that excel can recognize x as a multiple*?

    For example 5 x 6 = 5*6?

    • Excel Tips and Tricks from Pryor.com says:

      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.

Leave a Reply

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