Working with Excel, sometimes you would like to make a sum that only comes with specific cells (using SUMIF function), or calculate only specific cells in a row or column (using COUNTIF function). Below is the example to present a record of orders. You can see a bold heading prepared below the table for you to use a SUMIF function to get the sum of money expended by ABC Sisters.

order-list

The simple option you can get the total amount is by working with excel functions that contain conditions assembled into them. To get the outcome of that is certain to identify the result of a condition, Excel can analyze that by using SUMIF function. By using SUMIF function in Excel, you can easily and quickly to get the sum amount of certain criteria instead of spending time by manually calculating.

The Formula for Sumif Function in Excel

Sumif is a function of Excel that can calculate only the total of the numbers in the Total column where the entry in the Customer column is ABC Sisters. Below you can see the model of the key formula that can be used to SUMIF function:

=SUMIF(range,criteria,sum_range)

The description of each of the arguments that used in SUMIF function:

  • RANGE is the selection of cells you would like to analyze.
  • CRITERIA is the conditions that can be a number, key phrase, or word that specifies which cells are going to be incorporated. For instance, criteria may be in the form as 53, “53”, “> 53”, “grapes” or B5.
  • SUM RANGE is the selected cells range to total. The formula will only sum the cells in the sum range if they are in accord with the range of the cells selected match the criteria. When you omit the sum range, the cells in the selected range are summed. Take a look at the formula for a real practice in using SUMIF to get the total amount of money of ABC Sisters that listed in column F, with the range is started from cell F3 until F17.

=SUMIF(B3:B17,“ABC Sisters”,F3:F17)

By using the formula of SUMIF function above, you can get the total of ABC Sisters.

SUMIF-Formula

You can type the formula either by typing directly in the formula bar next to the fx symbol on the Excel, or by using the INSERT FUNCTION tool by clicking the fx symbol. By clicking the fx symbol, there sill be a dialog box that would appear like the picture below, and there you also can see any error in case you are incorrectly typing in the values or ranges.

SUMIF-Dialog-Box

The red cycle on the image above is the result of the total sum of how much money spend by ABC Sisters. In order the result appears in the cell next to the ABC Sisters Total, click OK.

Advertisements