August 25th in Functions by Excelbud .

SUMIF Function in Excel

The SUMIF function adds cells specified in range by a certain condition or criteria.

Excelbud

I'm Excelbud, owner and site operator for Excelbuddy.com Thank you for visiting and feel free to explore.

The SUMIF function adds cells specified in range by a certain condition or criteria.

The Syntax for SumIF Function:

=sumif(range,criteria,[sum_range])

Range: A range is a range of cells you want to apply a specific criteria against.

Criteria: The criteria determines which cells to add.

Sum_range: Cells to sum.

Example:

The following example shows how to properly use the SumIf Function. Basically, we are using the SumIf function in Cell D3 to determine how many runs the Tigers scored in Home. The example uses the range A3:A12. The SumIf function matches the word “Home” in the range A3:A12 then adds the runs in the Sum_range B3:B12. Since our criteria is “Home,” the SumIf function ignores all of the away games.

1

Other Examples:

=SUMIF(A3:A12,C3,B3:B12) —– The SumIf Function returns: 20

=SUMIF(A3:A12,”Home”,B3:B12) —– The SumIf Function returns: 20

=SUMIF(A3:A12,”Away”,B3:B12) —– The SumIf Function returns: 13

Follow Up:

If you have any questions or need something explained further, please comment below.

Excel: SumIf Function

In Excel, the SumIf function adds all numbers in a range of cells, based on a given criteria.

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 are the cells to sum.

Applies To:

  • Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Let’s take a look at an example:

Based on the Excel spreadsheet above:

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

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

5 Comments

  • Chafik
    August 28, 2009

    Thanks for tutorials
    well written and illustrated
    a must read..
    Page bookmarked

  • adijati
    October 23, 2009

    how many colom,for sum_range, if much?

  • Hema
    November 5, 2009

    It is too good, well illustrated.
    I liked, the way how presented.

    Thanks!!
    Hema

  • Excelbud
    November 6, 2009

    Your welcome Hema!

  • wes
    December 23, 2009

    thanks for this it enlightened me piz

Leave A Comment.