Browsing all articles tagged with Formula

SUMIF Excel Formula

Author Zaheer    Category Formulas     Tags , ,

Before learning how to use named ranges in  SumIf function, it’s a good idea to learn first how to use SumIf function. This function is utilized to sum up the data contained in cells within a selected array that meet a certain condition. The function’s formula is:

=SUMIF (Range, Criteria, Sum Range)

Definition of terms:

  • Range – this parameter is the group or array of cells that will be added up by the function.
  • Criteria – this parameter determines whether or not the cells are going to be counted.
  • Sum Range – this parameter is the array of data which is summed up when the first array have met  the certain condition. However, if this array is cancelled, the first array is the one that will be summed up.

For a better understanding about the use  of  SumIf function, here is an example:

  • Type in the following values into cells D1 to D6: 113, 164, 177, 142, 129, 164.
  • Then type in these values within cells E1 up to E6: 11, 21, 31, 11, 21, 31.
  • Click E7 cell, the result will appear in this cell.
  • On the ribbon, click the Formula Tab.
  • Then choose and click Math and Trig. After that, a drop down list will appear.
  • From the list, choose and click SUMIF, then dialog box of the function will appear.
  • Click on Range line from dialog box.
  • Drag D1 to D6 on your worksheet.
  • Type in 164 on Criteria line.
  • Then click on Sum Range.
  • Drag the cells E1 up  to E6 over your worksheet. Then click the OK button.
  • The result, which is 52, should be displayed in cell D7. Since only 2 cells (D2 and D6) meet the criteria which equals “164″, only the cells that corresponds to them which are E6 and E2 are added up. The summed up value of 21 and 31 is 52.
  • When cell D7 is clicked, the complete formula of the function will appear in  formula bar on top of  the spreadsheet. The complete formula is: =SUMIF (D1 : D6, 164, F1:F6 ).

How to Use the Named Ranges in SumIf Function?

  • Delete all the cell contents of B1 and C1. Enter the word “Amounts” in cell B1 and “Names” in cell C1.
  • Type in 6, 22, 42, 52, 102 and 202 in cells B2 to B7 respectively. Then enter the names Jill, Dick, Fred, John, Dave and Bob in cells C2 to C7 respectively.
  • Select the range B1 to C7.
  • From the Menu bar, click Insert, then Name and finally click Create. The Create Names dialog box will appear and it is utilized to create names on the basis of the current region column and/or row headings. From the example, you only have column headings, so make sure that only the “Top Row” check box is checked.
  • What you have done in this process is to instruct Excel that you want to name the range B2:B7 with”Amounts” as well as the range C2:C7 with “Names”. In other words, you tell Excel to name the selected range with the headings in the top row. Then click the OK button.
  • Click Insert then select Function.
  • Select Math and Trig then locate SumIf function. The Sumif dialog box will appear.
  • In the Range argument’s dialog box, you have to click the collapse dialog button then select the range C2:C7. Ms Excel must put the name “Names” in the Range Argument box. Then you need to click over the Expand Dialog button.
  • In the Criteria Argument box, enter “D*”.
  • Then on the Sum_range Argument box, click the collapse dialog button then select the range B2:B7. MS Excel must put the name “Amounts” on the Sum_range Argument box. Click the Expand dialog button and then click the OK button.
  • The complete formula should be =SUMIF (Names, “D*”, Amounts) and the result should be 124.

The easiest and effective method of naming the ranges with the similar names as the row and/or column headings is using the Create Names dialog. But you can also type the names into the Name Box directly located on the left of formula bar. Then if you click over the small drop sign that is right on the Name Box, the named ranges Names and Amounts should appear. In case you select either of the two, MS Excel function will take you directly to the selected named range and Excel will select the range.