Note that the result, 84.50, is the same as the previous example. SUMPRODUCT(D2:D5,C2:C5) multiplies D2*C2, D3*C3, etc., and sums the results. Try the SUMPRODUCT function, which multiplies the cells in two ranges and sums the results. The total in cell E7 sums up the extended prices.īut 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? 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. Multiplication of Ranges with the SUMPRODUCT FunctionĬonsider the following invoice. If a cell in the range is zero, the product will be zero.ģ. If a cell in the range is empty or contains text, Excel leaves that cell’s value out of the calculation. In each case, Excel multiplies all the numbers to find the product. Any combination of numbers, formulas, cell references, and range references.Which is equivalent to =F3*F4*F5*(and so on, all the way up to)*F25, or: A range of cells containing numbers, or multiple ranges separated by commas, such as:.Cell references separated by commas, such as:.Numbers or formulas separated by commas, such as:.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. 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.”Ģ. Then it multiplies 3*8*6 and reaches 144. In the case of “=3*(3+5)*6”, Excel adds 3 and 5 first, resulting in 8. Excel will always evaluate anything in parentheses before resuming the remaining calculations following PEMDAS. What if you want to add 3+5 before performing the multiplication? Use parentheses. 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. 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. You can mix and match the * with other arithmetic operators, such as addition (+), subtraction (-), division (/), and exponentiation (^). Use the same format to multiply the numbers in two cells: “=A1*A2” multiplies the values in cells A1 and A2.

To multiply 2 times 8, for example, type “=2*8”. To write a formula that multiplies two numbers, use the asterisk (*). Read on for three powerful ways to perform an Excel multiply formula. 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? While there is no “Excel multiply formula” there are multiple ways to multiply in Excel.

By Christy Perry Categories: Formulas Tags: Multiply Formula

Xel 3 used