Page 148 - Excel Workbook for Dummies

14_798452 ch09.qxp 3/13/06 7:52 PM Page 131
Chapter 9: Using Math Functions 131

9. Open the Solved9-3.xls workbook in the Chapter 9 folder and check the results in
your SUM formulas sheet against its formulas. If everything checks out, save
your workbook in the Chapter 9 folder with the filename Solved9-3-mine.xls and
then close both workbooks.

Conditional totals

All the variations of the SUM function you’ve used up to now calculate their totals come
rain or shine. The SUMIF function, however, is a little different: It only sums its desig-
nated values when a particular condition is true. The SUMIF function, although located
in the Math & Trig category, could have just as easily been classified as one of the
Logical functions (see Chapter 12) because it basically works only when its compara-
tive condition returns the logical value TRUE (refer to Chapter 12 for a refresher on cre-
ating comparative formulas that return TRUE or FALSE as their answers).
The syntax of the SUMIF function includes the following arguments:

SUMIF(range,criteria,[sum_range])

The range argument specifies the cells that you want Excel to evaluate using the
condition or conditions specified by the criteria argument. The optional sum_range
argument specifies the cells you want Excel to sum when the condition in the criteria
argument is found TRUE. You only need to specify a sum_range argument when the
cell range to be summed is not the same as the one whose values are evaluated as to
whether they meet the condition set up in the criteria argument.
To get some practice using the SUMIF function, you will work with the Jan-06 Sales
worksheet in the workbook containing the data list of the January 2006 sales for the
fictitious company, Chris’s Cookies. This data list tracks the sales by store location,
the type of baked item sold (lemon tarts, blueberry muffins, Lots of Chips cookies,
and strawberry pie), the date of sale, the number of dozens sold, the price per dozen,
and the daily sales total. Most of these fields tracked in the data list have range
names assigned to their data (Store_name to the data in the Store field, Date_sold to
the data in the Date field, Item_sold to data in the Item field, and Daily_sales to the
data in the Daily Sales field) that you can refer in place of cell ranges in the formulas
you construct using the SUMIF function.

Try It

Exercise 9-4: Using the SUMIF Function
Open the Exercise9-4.xls workbook file in your Chapter 9 folder in the My Practice
Spreadsheets folder inside My Documents on your hard disk or in the Excel
Workbook folder on the workbook CD-ROM. Use its Jan-06 Sales worksheet to practice
using the SUMIF function.

1. Position the cell cursor in cell I3 and construct a formula using the SUMIF
function that totals the daily sales for all lemon tarts sold in the month of
January 2006.
This SUMIF function uses the Item_sold range as its range argument and the
Daily_sales range as its sum_range argument. The condition for the criteria
argument is where the item sold is equal to lemon tarts, which is expressed as
follows:
“=lemon tarts”