SUMIF
The SUMIF function returns the sum of a collection of numbers, including only numbers that satisfy a specified condition.
SUMIF(test-values, condition, sum-values)
test-values: The collection containing the 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 wildcards 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 wildcard. condition can also contain a REGEX function instead of wildcards.
sum-values: An optional collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values. It should have the same dimensions as test-values.
Notes
If sum-values is omitted, the default value is test-values.
Although test-values can contain any value, it should usually contain values all of the same value type.
If sum-values is omitted, test-values would normally contain only number or duration values.
Examples |
|---|
Given the following table: |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | 1 | 10 | a | a | c |
2 | 2 | 20 | b | c | b |
3 | 3 | 30 | a | a | c |
4 | 4 | 40 | |||
5 | 5 | 50 | 1 | 5 | 9 |
=SUMIF(A1:A5, "<5") returns 10, because the sum of 1, 2, 3, and 4 (the values less than 5 (condition) included within the test-values range A1:A5) is 10. =SUMIF(A1:A5, "<>4") returns 11, because the sum of 1, 2, 3, and 5 (the values that are not equal to 4 (condition) included within the test-values range A1:A5) is 11. You can also use the ≠ operator. =SUMIF(A1:A5, "<5", B1:B5) returns 100, because the sum of 10, 20, 30, and 40 (the corresponding values in column B (sum-values is B1:B5) for those values in column A that are less than 5) is 100. =SUMIF(D1:E3, "=c", A3:B5) returns 84, because those cells in sum-values (cells B3, A4, and B5) that correspond to those cells in test-values that are equal to "c" (cells E1, D2, and E3) contain 30, 4, and 50, which sum to 84. =SUMIF(A1:D5, 1) or =SUMIF(A1:D5, SUM(1)) each return 2, the total of all the occurrences of 1 within the range. |
Example using REGEX |
|---|
Given the following table: |
A | B | |
|---|---|---|
1 | 45 | |
2 | 41 | Aaron |
3 | 29 | |
4 | 64 | |
5 | 12 | Sarah |
=SUMIF(B1:B5, REGEX("([A-Z0-9a-z._% -] )@([A-Za-z0-9.-] \.[A-Za-z]{2,4})"),A1:A5) returns 138, the sum of cells in A1:A5 where the corresponding cell in B1:B5 contains an email address. |