tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities

Excel Functions

(Scroll to see more)

Share this page:

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:

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.

Microsoft Excel

Then we've entered the following data in Excel:

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

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:

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