Returns the sum of a range that meets a given criteria. The function adds all of its arguments that meet the given criteria.
SUMIF(range, criteria, [sum_range])
- range - The range to which the criteria are to be applied.
- criteria - The pattern or test to apply to the range.
- sum_range - [OPTIONAL] The range to add up.
SUMIF(B1:B6,">5") returns the sum of such the numbers in the range B1 to B6 that are greater than 5.
SUMIF(B1:B6,">5",C1:C6) returns the sum of such numbers contained in C1:C6 whose corresponding values in range B1:B6 are greater than 5.
- Cells in range and sum_range that contain TRUE or FALSE are ignored.
- Ensure that criteria is enclosed in quotation marks.
- The range and sum_range need not be of the same size and shape. The actual cells that are summed are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:
- If range is A1:A25 and sum_range is B1:B20, the cells that will be summed are: B1:B25
- If range is A1:B25 and sum_range is B1:B20, the cells that will be summed are: B1:C25
- criteria can contain wildcard characters. Currently, the use of asterisk (*) is supported.
- An asterisk matches any sequence of characters.
- To match an actual asterisk, type a tilde (~) before the character. For example: (~*).