Microsoft Office Tutorials and References
In Depth Information
Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()
Note the following in this syntax:
sum_rangeis the range to sum.
criteria_range1, criteria_range2, ... are one or more ranges in which to
evaluate the associated criteria.
criteria1, criteria2, ... are one or more criteria in the form of a num-
ber, an expression, a cell reference, or text that define which cells
will be added. For example, they can be expressed as 32, "32", ">32",
"apples", or B4.
Each cell in sum_rangeis summed only if all the corresponding cri-
teria specified are true for that cell.
Cells in sum_rangethat contain TRUE evaluate to 1; cells in
sum_rangethat contain FALSE evaluate to 0.
You can use the wildcard characters question mark (?) and asterisk
(*) in criteria. A question mark matches any single character; an as-
terisk matches any sequence of characters. If you want to find an ac-
tual question mark or asterisk, you need to type a tilde (~) before the
character.
Unlike the range and criteria arguments in SUMIF, the size and shape
of each criteria_rangeand sum_rangemust be the same.
In Figure 11.33 , you want to build a table that shows the total by region
and product. sum_rangeis the revenue in H11:H5011. The first criteria pair
consists of the regions in \$C\$11:\$C\$5011 being compared to the word Eastin
B\$1. The second criteria pair consists of the divisions in \$B\$11:\$B\$5011 be-
ing compared to G854 in \$A2. The formula in B2 is =SUMIFS(\$H\$11:\$H\$5011,
\$C\$11:\$C\$5011,B\$1,\$B\$11:\$B\$5011,\$A2). You can copy this formula to B2:D6.
Search JabSto ::

Custom Search