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:
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:
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:
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 |
To find the sum of sales in the North area during quarter 1:
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.
I.e. this formula finds the sum of the values $223,000 and $125,000 (from cells D2 and D3).
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:
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.
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.
If you get an error from the Excel Sumifs function, this is likely to be the #VALUE! error:
| #VALUE! | - | Occurs if the supplied sum_range and criteria_range arrays do not all have equal length. |