The Excel SUMIFS Function

Related Functions:
AVERAGEIF
AVERAGEIFS
SUMIF

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

[sum_range] - An array of numeric values (or cells containing numbers) which are to be added together, if the corresponding range entries satisfy all the supplied criteria
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], ...

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:

- 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. "Name", "Thursday")
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 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.

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:

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

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


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


Sumifs Function Errors

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

Common Error
#VALUE! - Occurs if the length of any of the supplied criteria_range arrays is not equal to the length of the sum_range array.
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-2013 ExcelFunctions.net