Page 416 - Excel 2013 All-in-One For Dummies

398 Math & Trig Functions
To total the sales of Lemon tarts at all three locations in this data list, I created the following SUMIF formula in cell I3:
=SUMIF(item_sold,”=Lemon tarts”,daily_sales)
In this example, item_sold is the range name given to the cell range C3:C62, which contains the list of each item that has been sold in the first five days of January, 2010 (Lemon tarts, Blueberry muffins, Lots of chips cookies, or Strawberry pie), and daily_sales is the range name assigned to the cell range G3:G62, which contains the extended sales made at each store for each item.
The SUMIF formula in cell I3 then looks for each occurrence of “Lemon tarts” in the item_sold range (the criteria argument for the SUMIF function) in the Item column of the Cookie Sales list and then adds its extended sales price from the daily_sales range in the Daily Sales column to the total.
The formulas in cells I4, I5, and I6 contain SUMIF functions very similar to the one in cell I3, except that they substitute the name of the dessert goodie in question in place of the =Lemon tarts criteria argument.
Summing on multiple criteria with SUMIFS
Figure 5-3 illustrates the use of the SUMIFS function to apply multiple criteria in the summing of the daily sales. Here, I want to know the total of the sales of one item (Lemon tarts) at one store location (Anderson Rd.).
In order to do this, I created the following formula in cell I8, using the SUMIFS function:
=SUMIFS(daily_sales,item_sold,”Lemon tarts”,store,”Anderson Rd.”)
In this formula, the sum_range argument (specified first and not last as in SUMIF) is still the daily_sales cell range (G3:G62). The first criteria_range argument is item_sold (C3:C62) where the criteria is “Lemon tarts,” and the second criteria_range argument is store (B3:B62) where the criteria is “Anderson Rd.” When Excel evaluates the formula in cell I8, it applies both criteria so that the program ends up totaling only those daily sales where the item is Lemon tarts and the store location is Anderson Rd.
The formula in cell I9 immediately below in the worksheet shown in Figure 5-3 also uses the SUMIFS function, but this time applies just a single criteria in performing the summation. This formula sums the daily sales for any bakery item that is not a Strawberry pie:
=SUMIFS(daily_sales,item_sold,”<>Strawberry pie”)