The Excel SUMIF Function

Related Functions:

AVERAGEIF

AVERAGEIFS

SUMIFS


Basic Description

The Excel Sumif function finds values in a supplied array, that satisfy a given criteria, and returns the sum of the corresponding values in a second supplied array.

The syntax of the function is :

SUMIF( range, criteria, [sum_range] )

Where the function arguments are:

range - An array of values (or range of cells containing values) to be tested against the supplied criteria
criteria - The condition to be tested against each of the values in range
[sum_range] -

An optional array of numeric values (or cells containing numbers) which are to be added together, if the corresponding range entry satisfies the supplied criteria

If the sum_range argument is omitted, the values from the range argument are used instead

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character
*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The supplied criteria can be either:

- a numeric value (which may be an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2008, TRUE)
or
- a text string (eg. "Text", "Friday")
or
- an expression (eg. ">12", "<>0")

and can be supplied to the function either directly, as a reference to a cell, or as a value returned from another function or formula.

Note that if your criteria is a text string or an expression, this must be supplied to the function in quotes.

Also note that the Excel Sumif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be equal.


Excel Sumif Function Examples

Example 1

The following example shows the Excel Sumif function using critia based on text strings.

The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results from the function calls.

 Formulas:

  A B C
1 Month  Team  Sales 
2 Jan North 1 $36,693
3 Jan North 2 $22,100
4 Jan South 1 $53,321
5 Jan South 2 $34,440
6 Feb North 1 $29,889
7 Feb North 2 $50,090
8 Feb South 1 $32,080
9 Feb South 2 $45,500
10      
11 =SUMIF( A2:A9, "Feb", C2:C9 )
12 =SUMIF( B2:B9, "North 1", C2:C9 )
13 =SUMIF( B2:B9, "North*", C2:C9 )

 Results:

  A B C
1 Month  Team  Sales 
2 Jan North 1 $36,693
3 Jan North 2 $22,100
4 Jan South 1 $53,321
5 Jan South 2 $34,440
6 Feb North 1 $29,889
7 Feb North 2 $50,090
8 Feb South 1 $32,080
9 Feb South 2 $45,500
10      
11 $157,559    
12 $66,582    
13 $138,772    

In the above spreadsheet, each call to the Excel Sumif function tests the contents of cells A2 to A9 or cells B2 to B9 against the supplied criteria. For of the cells that satisfy the criteria, the corresponding cells in the range C2 to C9 are summed.

For example, the cells B2 and B6 are both equal to the text string "North 1". Therefore, the Sumif function in cell A12 adds together the corresponding values from cells C2 and C6 ($36,693 and $29,889), and returns the value $66,582.

Note that the function in cell A13 used the wildcard * and so the criteria is for the cells in the range B2-B9 to begin with the text string "North". This is satisfied by the values "North 1" and "North 2".


Example 2

The following example shows the Excel Sumif function using critia based on numerical values.

 Formulas:

  A B C
1 1 200  
2 2 45  
3 1 550  
4 2 450  
5 1 20  
6      
7 =SUMIF( A1:A5, 1, B1:B5 )
8 =SUMIF(B1:B5, ">100" )

 Results:

  A B C
1 1 200  
2 2 45  
3 1 550  
4 2 450  
5 1 20  
6      
7 700    
8 1200    

Note that:

Further examples of the Excel Sumif function can be found on the Microsoft Office website.


Sumif Function Errors

The error that you are most likely to get from the Excel Sumif function is the #VALUE! error :

Common Error
#VALUE! - Occurs if the supplied criteria is a text string that is more than 255 characters long
Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net