excel functions

MS Excel: SUMIF Function (WS)

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


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.


The syntax for the SUMIF function in Microsoft Excel is:

SUMIF( range, criteria, [sum_range] )

Parameters or Arguments

The range of cells that you want to apply the criteria against.
The criteria used to determine which cells to add.
Optional. It is the cells to sum. If this parameter is omitted, it uses range as the sum_range.

Applies To

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

  • Excel 2016, 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:

Microsoft Excel

Based on the Excel spreadsheet above, the following 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.

Microsoft Excel

Then we've entered the following data in Excel:

Microsoft Excel

Based on the Excel spreadsheet above, the following 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.

Microsoft Excel

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:


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:


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.