Related Function:
The SUMIFS function finds values in one or more supplied arrays, that satisfy a set of criteria, and returns the sum of the corresponding values in a further supplied array.
 This function was introduced in Excel 2007 and so is not available in earlier versions.
Syntax
=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2], … )
Arguments
Argument  Description  

sum_range  An a range of cells containing numbers, which are to be added together if the criteria are satisfied  
criteria_range1, [criteria_range2], … 
Ranges of cells containing values to be tested against the respective criteria1, criteria2, etc.


criteria1, [criteria2], … 
The conditions to be tested against the values in criteria_range1, [criteria_range2], etc.

Examples
A  B  C  D  E  F  G  

1  Quarter  Agent  Sales  Formula  Result  Notes  
2  1  Norton  $222,100  =SUMIFS(C2:C13,A2:A13,”>1″,B2:B13,”Sam”)  $768,104  Adds the sales made by Sam for quarters 2, 3, and 4  
3  1  Sam  $252,280  =SUMIFS(C2:C13,A2:A13,”<>2″,B2:B13,”Sarah”)  $707,399  Adds the sales by Sarah excluding the 2nd quarter  
4  1  Sarah  $222,100  
5  2  Norton  $251,280  
6  2  Sam  $229,785  
7  2  Sarah  $232,190  
8  3  Norton  $294,820  
9  3  Sam  $273,139  
10  3  Sarah  $219,167  
11  4  Norton  $258,142  
12  4  Sam  $265,180  
13  4  Sarah  $266,132 
Usage note: You can also use the following wildcards in text matches for the criteria argument:
? – matches any single character * – matches any sequence of characters e.g. the condition “A*e” will match any substring beginning with “A” and ending in “e”.
Common Function Error(s)
Problem  What went wrong 

#VALUE!  Occurs if the length of any of the supplied criteria_range arrays is not equal to the length of the sum_range array 