[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

SUMIF

Syntax:

SUMIF ( cell-range , selection-criteria [ , sum-range ] )

Description: Applies selection criteria on the values in one range of cells and sums the values of the cells in a corresponding range.

Arguments:

Name

Type

Description

cell-range

reference

Designates the range of cells to be inspected.

selection-criteria

number, expression, reference, text

Defines which cells will be counted. In the case of text, selection-criteria can consist of any comparison operator followed by the operand against which each cell's value is to be compared. selection-criteria can include one or more wildcard characters, question mark (?) and asterisk (*). A question mark matches any single character; an asterisk matches any sequence of characters. To search for a question mark, asterisk, or tilde character, prefix that character with a tilde (~).

sum-range

reference

If present, sum-range designates the cells whose values are summed. In this case, sum-range does not have to have the same size and shape as cell-range. The actual cells that are added 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 cell-range. If omitted, cell-range also designates the cells whose values are summed.

 

Return Type and Value: number – The sum of the cells corresponding to those selected.

[Example: Given that A1, B1, C1, and D1, respectively, contain the values 3, 10, 7, and 10

SUMIF(A1:D1,"=10") results in 20
SUMIF(A1:D1,">5") results in 27
SUMIF(A1:D1,"<>10") results in 10

Given that A2, B2, C2, and D2, respectively, contain the values apples, melons, 10, and 15
SUMIF(A2:B2,"*es",C2:D2) results in 10

end example]