Chapter 11: Excel Tutorial (Using SumProduct and SumIF Functions)

After completing the tutorial, you will be able to use the SumProductand SumIF functions within Excel. These functions are combinations of two different functions within one. The SumProduct function allows you to multiply corresponding rows/columns, and returns the sum of the products.

Using SumProduct Function – the following steps can be used to setup a SumProduct function.

  1. Open the Excel workbook labeled “Template.”
  2. You will enter the SumProduct function in cell F13. When you type in =SUMPRODUCT( Excel asks you for the arrays you would like to use. Here we are going to use the # of Units column and the Price column. You should enter the arrays as shown below. Your answers will not change if you enter one column before the other.

  1. You can double check your answer by summing the Total Price column. You will see that your total should be $3,380.

Using SumIF Function – the following steps can be used to setup a SumIF function.

  1. Using the sheet we already created,we will use the SumIF function to find the total for each item. Below where you typed in the SumProduct, you will now start to enter the SumIF functions.
  2. In cell F14 you will type in =SUMIF( now click the function button (). This will open a box that will help you use the function.
  3. Your range is the column or row that has the details that you would like to sum by, for this it is column D. The criteria are the specific detail that you would like to find within the range, this is “Item 1”. To make entering easier, type in this field E14, this will allow you to drag the formula down for the other criteria. The Sum_range will be column F or the total price. Below is the Function Arguments window showing the exact formula:

  1. As shown above, in the Range and Sum_range the array is locked by $ signs, this will lock the data to this range and not allow them to shift during the dragging of the formula entered in F14. You can type these in manually or select each cell reference (D2) and hit the F4 key.
  2. You have successfully entered in the SumIF function into cell F14. You see that the total is $1,050. When you select the cell you should see , click on the box in the lower right hand corner of the cell () and drag down to cell F17 as shown below and let go of the left mouse button:
  1. When you dragged the function down, the only reference that changed was your criteria. This changed from the original E14 to E15, E16 and E17.
  2. Your results should match the ones below:

To do this calculation without this formula would be unwieldy with respect to entering IF functions for each row, and then summing these IF statements. Formulas as the ones shown above can help the user cut down the time spent creating a spreadsheet, and more time analyzing the data.