Excel 2010: Using SUMPRODUCT Function

Excel SUMPRODUCT is one of the most used function. Because of it’s versatility, it can be used in numerous ways. This function can handle array in a simple way, giving novices an option to use array without comprehending the structure of it. It can manipulate up to 25-30 arrays, requires list of array as argument; =SUMPRODUCT(array1, array2….). So you just need to specify range of array in your worksheet. Primarily it is used for adding and then multiply the values stored in array. This post will be putting some light on how to use this function in your spreadsheet.

Launch Excel 2010 and open a spreadsheet on which you want to apply SUMPRODUCT function. For Instance, we have included a spreadsheet, containing records of Item & it’s respective price and number of products to be delivered, and corresponding charges as; Items, Delivery, Price, and Delivery Charges, as shown in the screenshot below.

table

Now we want to find out Total amount to be paid by the customers, including all the charges from every city. For evaluating total amount, old-school method can be used, but it would be hectic to write one. We will be using SUMPRODUCT function to evaluate the Total amount. We will start off with including row label Total Amount beneath the table and in it’s adjacent cell we will be writing the formula.

total

For calculating total amount we will write formula like this;

=SUMPRODUCT(C2:D11,E2:F11)


The first argument in the formula is C2:D11, this marks up the location of array1, which contains Items & Delivery field, and the second parameter E2:F11 which contains items price and delivery charges.

For example we need this sort of calculation for desired result; (56*100)+(2*100) and so on. It will evaluate by, multiplying number of items sold with it’s respective Prices and Delivery with its Delivery charges, as shown in the screenshot below.

total amount

Now we will be changing the value into currency value. From Home tab, under Number group, click drop-down button, and click Currency.

number

table

You can also check out previously reviewed Excel Functions; SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, and LEN.

Advertisement
  • Pingback: Excel 2010: Radians and Degrees Function

  • Pingback: Excel 2010: CHOOSE Function

  • Pingback: Excel 2010: Matching Values With EXACT Function

  • Pingback: Excel 2010: Address Function

  • Pingback: Excel 2010: PROPER Function

  • Pingback: Excel 2010: Change Values Into Time (TIME function)

  • Pingback: Excel 2010 New RANK functions

  • http://www.facebook.com/people/Gymn-Bow/100000367756540 Gymn Bow

    it’s reads it is,  its is the possessive form.

  • http://profile.yahoo.com/LUB42GOU4SZXNSLYRIH664B43Q Publius

    A very handy use of the SUMPRODUCT fn is to return Total Taxation from a table of Marginal Tax Brackets and their associated Rates, eg:
       Given      Tax Threshold   Marginal Tax Rate   Differential Rate       Tax Threshold), (Taxable Income-Tax Threshold), Differential Rate) 
       Note inclusion of the double minus sign.

    • M B Sridharan

      Why we use double minus?

  • Dave

    the formula does not return what he shows-Column F is used in the array but he doesn’t reference how it is used in the calculation – given his example I do not understand this function any better than I did before I saw this example – 0 stars for usefulness

  • saleslady

    I need help. I am trying to caluculate commission. I want to have excel determine which comission rate to use based on “SALE” or “RENTAL”. Sales are comissionable at 5% and Rental at 10%

    Invoice Amount Sale or Rental Comission
    $6572.52 RENTAL ?

  • Deb

    For example we need this sort of calculation for desired result; (56*100)+(2*100) and so on. Should this not say (56*100)+(2*50)?

    • Tim

      Yes

  • Deb

    I get 100888 from your example instead of 97862.

    • Tim

      You will get 97862 if you change the price to match what he has in the second table

  • Russ A.

    There is a typo in the following instructions:

    For example we need this sort of calculation for desired result; (56*100)+(2*100) and so on. It will evaluate by, multiplying number of items sold with it’s respective Prices and Delivery with its Delivery charges, as shown in the screenshot below. CALCULATION OF ROW 2 SHOULD READ “(56*100)+(2*50)”.