The SUMIF function in Microsoft Excel 2019

The SUMIF function calculates the total of a range ofcells where certain conditions are met. Using the example inthe SSGProductSUMIF.xlsx workbook, we will demonstrate howthe syntax of the formula is constructed.

In column B, we have a list of products. In the list, theproduct name Fudgi Browno occurs morethan once. The SUMIF function will quickly add up all thesales values if the range B3:B44 contains the text Fudgi Browno to obtain the product totalsales. If the amounts in the list change for Fudgi Browno after the formula isconstructed, the formula will automatically update to include the newsales values in the cell containing the SUMIF function.

The syntax of the SUMIF function is shown in the followingdiagram:

ms office 652

When typing in formula criteria, enter any text used for the criteriabetween inverted commas (not the single but the double kind).Enter the SUMIF function directly into the cell, or choosefrom the list of Math & Trigfunctions in the Function Library.Then, proceed as follows:

  1. Open the SSGProductSUMIF.xlsxworkbook. 
  2. Click on cell J4 on the worksheet to construct the formula to workout the total sales for the product named Straubory.
  3. We can use the type directly in a cell method to construct theformula if you feel confident (the formula you need to enter would be=SUMIF(B3:B44,”Straubory”,E3:E44), orlocate the function from the Math &Trig book, located in the FunctionLibrary. Click to choose the SUMIF function from the Maths & Trig drop-down list, asillustrated in the following screenshot:

ms office 577

  1. The Function Arguments dialog boxis populated over the worksheet area, and the formula has started tobuild in the answer cell automatically.
  2. Make sure you click into the Rangeplaceholder, then move to the workbook and select the range B3:B44.
  3. Once you have the range selected, click back into the Function Arguments dialog box and click intothe Criteria placeholder and typeStraubory. Excel will fill in the inverted commas for youautomatically.
  4. Click into the Sum_rangeplaceholder area, and then drag the cell range E3:E44 on the worksheet to add up all thesales values, as illustrated in the following screenshot:
ms office 428
  1. Return to the Function Argumentsdialog box.
  2. Click on OK to confirm thearguments and enter the formula into the cell.
  3. This time, we will use the type in the cell method to find the totalsales for all the products excluding Straubory.
  4. Click on cell J6 to construct the formula as follows: =SUMIF(B3:B44,”<>Straubory”,E3:E44).
  5. Press the Enter key to see the formula result.

Leave a Comment