[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
COUNTIFS ( count-range , cell-range-1 , selection-criteria-1
[ , cell-range-2 , selection-criteria-2 [ , … ] ] )
Description: Counts the number of cells within a range that meet multiple criteria.
Arguments:
|
Name |
Type |
Description |
|
count-range |
Designates the cells whose values are included. count-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 count-range as the beginning cell, and then including cells that correspond in size and shape to cell-range-1 through cell-range-n. | |
|
cell-range-1 |
Designates the first range of cells to be inspected. Each cell in a range is counted only if all of the corresponding criteria specified are true for that cell. | |
|
selection-criteria-1 |
Designates the first range of cells to 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 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 |
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 |
If a cell in any argument is an empty cell, it is treated as if it had the value 0.
Return Type and Value: number – The count of the cells corresponding to those selected.
[Example: Given the following data:
|
|
A |
B |
C |
D |
|
1 |
Sales Person |
Exceeded Tables Quota |
Exceeded Chairs Quota |
Exceeded Desks Quota |
|
2 |
Davolio |
Yes |
No |
No |
|
3 |
Buchanan |
Yes |
Yes |
No |
|
4 |
Suyama |
Yes |
Yes |
Yes |
|
5 |
Leverling |
No |
Yes |
Yes |
COUNTIFS(B2:D2,"=Yes") results in 1 (counts how many times Davolio exceeded a sales quota for tables, chairs, and desks)
COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes") results in 2 (counts how many sales people exceeded both their tables and chairs quota)
COUNTIFS(B5:D5,"=Yes",B3:D3,"=Yes") results in 1 (counts how many times Leverling and Buchanan exceeded the same quota for tables, chairs, and desks)
end example]