# The Excel SUMIFS Function

Related Functions:
SUMIF
AVERAGEIF
AVERAGEIFS

## 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 Sumifs function is:

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

Where the function arguments are:

 sum_range - An array of numeric values (or a range of cells containing numbers) which are to be added together if the criteria are satisfied. criteria_range1[criteria_range2], ... - Arrays of values (or ranges of cells containing values) to be tested against the respective criteria1, criteria2, ... (The supplied criteria_range arrays must all have the same length as the sum_range). criteria1,[criteria2], ... - The conditions to be tested against the values in criteria_range1, [criteria_range2], ...

Note that:

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

Wildcards

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

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

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

E.g. 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:

• a numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2008, TRUE)
or
• a text string (e.g. "Name", "Thursday"), which can include wildcards (see right)
or
• an expression (e.g. ">12", "<>0").
• If your criteria is a text string or an expression, this must be supplied to the Sumifs function in quotes;
• 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.

A B C D
1 Quarter Area Sales Rep. Sales
2 1 North Jeff \$223,000
3 1 North Chris \$125,000
4 1 South Carol \$456,000
5 2 North Jeff \$322,000
6 2 North Chris \$340,000
7 2 South Carol \$198,000
8 3 North Jeff \$310,000
9 3 North Chris \$250,000
10 3 South Carol \$460,000
11 4 North Jeff \$261,000
12 4 North Chris \$389,000
13 4 South Carol \$305,000

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

• The value in column A is equal to 1
and
• The entry in column B is equal to "North"

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

I.e. 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:

• The value in column A is greater than 2
and
• The entry in column C is equal to "Jeff"

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

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

For further examples of the Excel Sumifs function, see the Microsoft Office website.

## Sumifs Function Error

If you get an error from the Excel Sumifs function, this is likely to be the #VALUE! error:

Common Error
 #VALUE! - Occurs if the supplied sum_range and criteria_range arrays do not all have equal length.