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
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.