Conditionally sums the contents of cells in a range.
Syntax:
SUMIF(test_range, condition, sum_range)
This function identifies those cells in the range test_range that meet the condition, and sums the corresponding cells in the range sum_range. If sum_range is omitted the cells in test_range are summed.
condition may be:
a number, such as 34.5
an expression, such as 2/3 or SQRT(B5)
a text string
SUMIF looks for cells in test_range that are equal to condition, unless condition is a text string that starts with a comparator:
>, <, >=, <=, =, <>
In this case SUMIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).
For example the condition “>4.5” tests if the content of each cell is greater than the number 4.5, and the condition “<dog” tests if the content of each cell would come alphabetically before the text dog.
Blank (empty) cells in test_range are ignored (they never satisfy the condition).
condition can only specify one single condition.
Example:
SUMIF(A1:A9, "<0")
returns the sum of the negative numbers in A1:A9.
SUMIF(A1:A9, F1)
where F1 contains the text >=0 (without double quotes) returns the sum of the positive numbers in A1:A9.
SUMIF(B2:B4,"<"&F2, C2:C4)
where F2 contains 10 and cells B2, B3, B4 contain 7, 9, 11, returns the sum of C2 and C3, because cells B2 and B3 are less than 10.
SUMIF(D1:D9, "apples", E1:E9)
where cells in D1:D9 contain either apples or pears and cells in E1:E9 contain the corresponding quantities of each fruit, returns the total quantity of apples.
Advanced topic:
SUMPRODUCT(D1:D9="Apples", E1:E9).
returns the sum of entries in E1:E9 whose corresponding entry in D1:D9 matches Apples, with case sensitivity (entries of apples and APPLES would not match).