# MS Excel: SUMIF Function (WS)

This Excel tutorial explains how to use the Excel SUMIF function with syntax and examples.

## Description

The Microsoft Excel SUMIF function adds all numbers in a range of cells, based on a given criteria.

If you wish to apply multiple criteria, try using the SUMIFS function.

## Syntax

The syntax for the Microsoft Excel SUMIF function is:

`SUMIF( range, criteria, [sum_range] )`

### Parameters or Arguments

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

The SUMIF function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

## Type of Excel Function

The SUMIF function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

## Example (as Worksheet Function)

Let's look at some Excel SUMIF function examples and explore how to use the SUMIF function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel SUMIF examples would return:

```=SUMIF(A2:A6, D2, C2:C6)
Result: 218.6

=SUMIF(A:A, D2, C:C)
Result: 218.6

=SUMIF(A2:A6, 2003, C2:C6)
Result: 7.2

=SUMIF(A2:A6, ">=2001", C2:C6)
Result: 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 spreadsheet above, the following Excel SUMIF examples would return:

```=SUMIF(family, C2, B:B)
Result: 218.6

=SUMIF(family, ">=2001", B:B)
Result: 12.6```

To view named ranges: Under the Insert menu, select Name > Define.

Question: I have a question about how to write the following formula in Excel.

I have a few cells, but I only need the sum of all the negative cells. So if I have 8 values, A1 to A8 and only A1, A4 and A6 are negative then I want B1 to be sum(A1,A4,A6).

Answer: You can use the SUMIF function to sum only the negative values as you described above. For example:

`=SUMIF(A1:A8,"<0")`

This formula would sum only the values in cells A1:A8 where the value is negative (ie: <0).

Question:In Microsoft Excel I'm trying to achieve the following with IF function:

If a value in any cell in column F is "food" then add the value of its corresponding cell in column G (eg a corresponding cell for F3 is G3). The IF function is performed in another cell altogether. I can do it for a single pair of cells but I don't know how to do it for an entire column. Could you help?

At the moment, I've got this:

`=IF(F3="food"; G3; 0)`

Answer:This formula can be created using the SUMIF formula instead of using the IF function:

`=SUMIF(F1:F10,"=food",G1:G10)`

This will evaluate the first 10 rows of data in your spreadsheet. You may need to adjust the ranges accordingly.

I notice that you separate your parameters with semi-colons, so you might need to replace the commas in the formula above with semi-colons.

Share: