[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

SUMIFS

Syntax:

SUMIFS ( sum-range , cell-range-1 , selection-criteria-1
[ , cell-range-2 , selection-criteria-2 [ ,] ] )

Description: Adds the cells in a range that meet multiple criteria.

Arguments:

Name

Type

Description

sum-range

reference

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-1 through cell-range-n. 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-1 through cell-range-n. Each cell in sum-range is summed only if all of the corresponding criteria specified are true for that cell. Cells in sum-range that contain TRUE evaluate to 1; cells in sum-range that contain FALSE evaluate to 0.

cell-range-1

reference

Designates the first range of cells to be inspected.

selection-criteria-1

number, expression, reference, text

Specifies the criteria for the first range of cells that will be counted. In the case of text, selection-criteria-1 can consist of any comparison operator followed by the operand against which each cell's value is to be compared. selection-criteria-1 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 (~).

cell-range-n

reference

The optional arguments selection-criteria-2 through selection-criteria-n have corresponding arguments cell-range-2 through cell-range-n, and have the same semantics as selection-criteria-1 and cell-range-1, respectively.

selection-criteria-n

number, expression, reference, text

 

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

[Example: Given the following data:

 

A

B

C

D

1

Sales Person

Tables

Chairs

Desks

2

Emilio

34

85

97

3

Julie

353

23

18

4

Hans

13

67

14

5

Frederique

0

98

0

 

SUMIFS(B2:C5,A2:A5,"=Julie") results in 353 (the sum of the number of tables and chairs sold by Julie)

SUMIFS(B2:B5,A2:A5,"=Julie",A2:A5,"=Hans") results in 0 (the sum of the number of tables sold by Julie and Hans)

SUMIFS(B2:B5,A3,"=Julie",A4,"=Hans") results in 34 (the sum of the the number of tables sold by Julie and Hans)

SUMIFS(B2:D5,A2:A5,"<>Emilio") results in 768 (the sum of the number of tables, chairs, and desks sold by all sales persons except Emilio)

end example]