MS Excel: SUMIF Function (WS)
In Microsoft Excel, the SUMIF function adds all numbers in a range of cells, based on a given criteria.
Syntax
The syntax for the SUMIF function is:
SUMIF( range, criteria, [sum_range] )
range is the range of cells that you want to apply the criteria against.
criteria is used to determine which cells to add.
sum_range is optional. It is the cells to sum. If this parameter is omitted, the SUMIF function uses range as the sum_range.
Applies To
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Function
- Worksheet function (WS)
Worksheet Function Example
Let's take a look at an example to see how you would use the SUMIF function in a worksheet:
Based on the Excel spreadsheet above, the SUMIF function would return the following:
| =SUMIF(A2:A6, D2, C2:C6) | would return 218.6 |
| =SUMIF(A:A, D2, C:C) | would return 218.6 |
| =SUMIF(A2:A6, 2003, C2:C6) | would return 7.2 |
| =SUMIF(A2:A6, ">=2001", C2:C6) | would return 12.6 |
Using Named Ranges
You can also use a named range in the SUMIF function. For example, we've created a named range called family that refers to column A in Sheet 1.
Then we've entered the following data in Excel:
Based on the Excel spreadsheet above, the SUMIF function would return the following:
| =SUMIF(family, C2, B:B) | would return 218.6 |
| =SUMIF(family, ">=2001", B:B) | would return 12.6 |
To view named ranges: Under the Insert menu, select Name > Define.
