SUMIF

Adds the cells specified by a given criteria.

Syntax

SUMIF(range,criteria,sum_range)

Range     is the range of cells that you want evaluated by criteria.

Criteria     is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".

Sum_range     are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.

Remarks

  • Sum_range does not have to be the same size and shape as range. The actual cells that are added are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:
If range is And sum_range is Then the actual cells are
A1:A5 B1:B5 B1:B5
A1:A5 B1:B3 B1:B5
A1:B4 C1:D4 C1:D4
A1:B4 C1:C2 C1:D4
  • You can use the wildcard characters, question mark (?) and asterisk (*), in 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 (~) preceding the character.
  • Microsoft Office Excel provides additional functions that you can use 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.
    • To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, 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 (DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP).

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
 
1
2
3
4
5
A B
Property Value Commission
100,000 7,000
200,000 14,000
300,000 21,000
400,000 28,000
Formula Description (Result)
=SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for property values over 160000 (63,000)
=SUMIF(A2:A5,">160000") Sum of the property values over 160000 (900,000)
=SUMIF(A2:A5,"=300000",B2:B3) Sum of the commissions for property values over 160000 (21,000)
 
 
Applies to:
Excel 2003