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.