Microsoft Office Tutorials and References

In Depth Information

**Using conditional functions**

Figure 12-39
Conditional sum formulas are easily created using the SUMIF function.

Youâ€™ll find the NorthwindTransactions.xlsx file with the other examples on the companion

website.

You can use the SUMIF function to collect a total of items in a range that match a given

criterion. In Figure 12-39, the salespersonâ€™s name is used as the criterion to collect sales totals

(of course, correct spelling and punctuation is key). If you want to be even more selective,

you can use the SUMIFS function, which allows you to specify up to 127 separate sets of

criteria. Figure 12-40 shows a SUMIFS function in the formula bar that uses two criteria:

salesperson and location. For each criterion, there are two arguments in the formula: one

defines the range of cells containing the values from which you want to gather totals, and

the other specifies the criterion identifying values you want to include.

As you might imagine, the COUNTIF and COUNTIFS functions operate similarly to their

SUM-family brethren, but instead of adding values together, they count
instances
. For

example, Figure 12-41 shows a worksheet displaying the number of times each salesperson

worked at each store location. The result cells in the Worked At Location area each contain

a COUNTIFS function that looks at both the salesperson and the location, and they return

not a total but a tally of the number of times this combination occurs.