How to sum cells by criteria

Excel 2016 2013 2010 2007 2003
Use SUMIF if you need to sum values for a particular person or another criterion.

To sum cells by criteria, do the following:

   1.   Select the cell that will contain the result.

SUMIF example Excel 365

   2.   Do one of the following:

  • On the Formula tab, in the Function Library group, select the Math & Trig button:
    Math and Trig Formulas in Excel 365

    Choose COUNTIF in the list.

  • Click the Insert Function button Insert Function button on the left of the Formula bar:
    Edit bar in Excel 365

    In the Insert Function dialog box:

    • select Math & Trig in the Or select a category drop-down list,
    • select SUMIF in the Select a function list:
    Insert Function Excel 365

   3.   In the Function Arguments dialog box:

Function Arguments in Excel 365
  • The Range field determines the range of cells Excel will look to perform the count in. In this example, the cell range is B2:B17.
  • The Criteria is a conditional statement similar to the conditional statement in the IF statement.
  • The Sum_range field tells Excel which cells to add when the criteria are met for each cell in the range. In this example, the cell range is D2:D17.

   4.   Press OK:

SUMIF example Excel 365

Notes:

  • You can enter this formula using the keyboard, for this example:

    = SUMIF (B2:B17, "*Revay", D2:D17)

  • You can use the wildcard characters: the question mark (?) and the asterisk (*) in the criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
  • Microsoft Excel provides additional functions that can be used to analyze your data based on a condition or criteria:
    • To count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function (see How to count cells by criteria for more details).
    • To have a formula return one of two values based on a condition, use the IF function.
    • To analyze data in a list based on criteria, such as profit margins or product types, use the database and list management functions (DCOUNT, DCOUNTA, DSUM, etc.).

See also this tip in French: Comment calculer la somme des cellules par critères.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.