SUMIFS
The SUMIFS function returns the sum of the cells in a collection where the test values meet the given conditions.
SUMIFS(sum-values, test-values, condition, test-values…, condition…)
sum-values: A collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values.
test-values: A collection containing values to be tested. test-values can contain any value.
condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE. condition can include comparison operators, constants, the ampersand concatenation operator, references and wildcards. You can use wild cards to match any single character or multiple characters in the expression. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wild card. condition can also contain a REGEX function instead of wildcards.
test-values…: Optionally include one or more additional collections containing values to be tested. Each test-values collection must be followed immediately by a condition expression. This pattern of test-values, condition can be repeated as many times as needed.
condition…: If an optional collection of test-values is included, condition… is an additional expression that results in a boolean value TRUE or FALSE. There must be one condition following each test-values collection; therefore, this function will always have an odd number of arguments.
Notes
For each of the test and condition value pairs, the corresponding (same position within the collection) cell or value is compared to the condition. If all the conditions are met, the corresponding cell or value in sum-values is included in the sum.
All collections must be of the same size.
Examples |
|---|
Given the following table: |
A | B | C | |
|---|---|---|---|
1 | Tonnes | Rating | Date |
2 | 6 | 1 | 10/12/2012 |
3 | 15 | 2 | 10/12/2012 |
4 | 5 | 1 | 13/12/2012 |
5 | 7 | 2 | 13/12/2012 |
6 | 8 | 2 | 14/12/2012 |
7 | 6 | 1 | 15/12/2012 |
8 | 7 | 2 | 15/12/2012 |
9 | 4 | 2 | 16/12/2012 |
10 | 7 | 1 | 16/12/2012 |
11 | 8 | 2 | 16/12/2012 |
12 | 5 | 1 | 17/12/2012 |
13 | 11 | 2 | 20/12/2012 |
=SUMIFS(A2:A13, B2:B13, “=1”, C2:C13, “>=13/12/2012”, C2:C13, “<=17/12/2012”) returns 23, the number of tonnes of the commodity delivered during the week of 17 December that were rated 1. The collection A2:A13 contains the number of tonnes, the item to be summed (sum-values). The collection B2:B13 (test-values) contains the first value to be tested, a rating of 1 (condition). The collection C2:C13 (test-values…) contains the second value to be tested, a starting date (condition…). This same range also contains the third value to be tested, an ending date. Rows 4 (5 tonnes), 7 (6 tonnes), 10 (7 tonnes) and 12 (5 tonnes) satisfy all conditions and the sum of these is 23 tonnes. =SUMIFS(A2:A13, B2:B13, “=2”, C2:C13, “>=12/12/2012”, C2:C13, “<=17/12/2012”) returns 34, the number of tonnes of the commodity delivered during the same week that were rated “2”. |
Example using REGEX |
|---|
Given the following table: |
A | B | |
|---|---|---|
1 | 45 | john@appleseed.com |
2 | 41 | Aaron |
3 | 29 | janedoe@appleseed.com |
4 | 64 | jake@appleseed.com |
5 | 12 | Sarah |
=SUMIFS(A1:A5, B1:B5,REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})",), A1:A5, ">10") returns 138, the sum of cells in A1:A5 that are larger than 10 and where the corresponding cell in B1:B5 contains an email address. |