Excel - Microsoft Excel SUMIFS Function

Description

The Excel 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. 

The function is new in Excel 2007, and so is not available in earlier versions of Excel. 

The syntax of the function is : 

SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... ) 

Where the function arguments are: 

The function can handle up to 127 pairs of criteria_range and criteria arguments. 

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

Each of the supplied criteria can be either: 

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 Sumifs function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be equal. 

Excel Sumifs Function Examples

The spreadsheet below shows the quarterly sales figures for 3 sales representatives. 

The Sumifs function can be used to find total sales figures for any combination of quarter, area and sales rep. 

This is shown in the examples below. 

Example 1

To find the sum of sales in the North area during quarter 1: 

=SUMIFS( D2:D13, A2:A13, 1, B2:B13, "North" ) 

which gives the result $348,000. 

In this example, the Excel Sumifs function identifies rows where: 

and calculates the sum of the corresponding values from column D. 

ie. this formula finds the sum of the values $223,000 and $125,000 (from cells D2 and D3). 

Example 2

Again, using the data spreadsheet above, we can also use the Sumifs function to find the total sales for "Jeff", during quarters 3 and 4: 

=SUMIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" ) 

This formula returns the result $571,000. 

In this example, the Excel Sumifs function identifies rows where: 

and calculates the sum of the corresponding values in column D. 

ie. this formula finds the sum of the values $310,000 and $261,000 (from cells D8 and D11). 

Sumifs Function Errors

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

Common Error