# MS Excel: SUMIF Function (WS)

Learn 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) | 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 spreadsheet above, the following Excel **SUMIF examples** would return:

=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.

## Frequently Asked Questions

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.