Two-condition formulas: SUMIFS vs. SUMPRODUCT.
Excel 2003 and Earlier
If you need to sum a data set based on one condition, the old SUMIF function will handle this calculation. The syntax of the SUMIF formula is =SUMIF(criteria range, criteria, sum range). The criteria range contains the data you want to check against the criteria, and the sum range contains the values to add when the criteria range matches the criteria. Naturally, the criteria range and the sum range need to be a similar size and shape. In Figure 1, for example, the formula =SUMIF(A2:A9,"East",C2:C9) provides the total revenue for the East region.
[FIGURE 1 OMITTED]
While SUMIF and COUNTIF are useful, they can't handle more than one condition. If you need to find total revenue where the region is East and the product is A, the old functions aren't up to the task.
Excel 2007 to the Rescue
In Excel 2007, Microsoft included AVER-AGEIF to go along with the old COUNTIF and SUMIF functions and, more important to this discussion, added plural versions of all three functions.
The SUMIFS function works like SUMIF, but it can handle up to 127 different criteria conditions. Because of the additional conditions, Microsoft rearranged the argument list for SUMIFS. It starts with the sum range as the first argument instead of the final argument and is then followed by a pair of arguments (criteria range and the criteria for that range) for each condition. The syntax is =SUMIFS(sum range, criteria range 1, criteria 1, criteria range 2, criteria 2, ... criteria 127, criteria range 127).
In Figure 1, the formula for finding total revenue for product A in the East region would be:
The first criterion checks to see if column A is "East," and the second criterion checks to see if column B is product "A." If both conditions check out, then it adds the corresponding value from column C.
It was excellent of Microsoft to add this new functionality to Excel 2007, but many people have been slow to upgrade from previous versions. If you use this function and send the workbook to someone using an old version of Excel, the function will become a #NAME? error. Unless everyone in your company has upgraded to Excel 2007, it isn't really safe to use SUMIFS yet. Luckily, Excel 2003 and older versions offer a way to handle multiple conditions using SUMPRODUCT.
Handling Multiple Conditions with SUMPRODUCT
Figure 2 shows how to express a conditional sum in several steps. Suppose you want to add up the revenue for the East region and product A. The formulas in columns E and F return either a TRUE or FALSE. For example, the formula in cell E2 (=A2="East") returns a TRUE because the region in A2 is "East." The formula in cell F2 (=B2="A") returns a FALSE, however, because the product in B2 is not "A."
[FIGURE 2 OMITTED]
The cells in column G then multiply the TRUE/FALSE in column E by the TRUE/FALSE in column F by the revenue in column C. When you use a TRUE/FALSE value in a multiplication statement, Excel converts the TRUE values to 1 and the FALSE values to 0. In cell G4, for example, the formula (=E4*F4*C4) converts to =1 x 1 x $4,000. Thus the value in cell G4 is $4,000. In row 5, however, the region isn't "East," making E5 return FALSE. Therefore, the formula in G5 becomes 0 x 1 x $8,000, which results in zero. Finally, cell G10 sums the products in G2:G9, arriving at a final total that matches our conditions. Because anything that doesn't match our conditions becomes a zero, we don't have to worry about it affecting the sum.
All of that work can also be accomplished with the SUMPRODUCT formula, which is a specialized function in Excel that multiplies corresponding values from multiple arrays and then sums those products. You might wonder who originally needed a function to do this, but it turns out that it's perfect for expressing all of the logic in range E2:G10 with one formula:
Excel will evaluate the first array (A2:A9="East") and produce a temporary result in memory that looks similar to the results shown in E2:E9. The second array will produce a temporary result similar to F2:F9. When Excel multiplies these two temporary arrays by the revenue in C2:C9, it will produce the values shown in G2:G9. Finally, the function sums those values, creating the sum shown in G10.
One important item to notice in the formula is the use of the multiplication signs.
Excel Help says that each array should be separated by commas, but when you use commas, the SUMPRODUCT function converts all nonnumeric values to zero--including the TRUE values. Using multiplication signs between the arrays converts TRUE values to "1" before SUMPRODUCT begins evaluating.
Note that some people prefer to use a double minus before each TRUE/FALSE array. This is another way of changing any TRUE values to "1" before the SUMPRODUCT begins its work:
Generalizing the Formula
In Figure 1, the table in rows 11 to 13 shows the revenue for every combination of region and product. In this example, you wouldn't hard-code the region (East) and product (A) values but would instead point to various cells. Enter this formula in B12 and copy to cells B12:C13:
=SUMPRODUCT(($A$2:$A$9=$A12)* ($B$2:$B$9=B$11) *($C$2:$C$9))
It's always fun to try out new functionality in the latest version of Excel. While SUMIFS is a great improvement, you might need to use SUMPRODUCT until everyone who uses your workbooks upgrades to Excel 2007.
Bill Jelen will bring his Power Excel seminar to IMA's 90th Annual Conference & Exhibition. Send questions for future articles to IMA@MrExcel.com.
Excel is a topic at IMA's Annual Conference, June 6-10, 2009, in Denver, Colo. For information, visit www.imaconference.org.
|Printer friendly Cite/link Email Feedback|
|Title Annotation:||Microsoft Excel 7.0|
|Date:||Mar 1, 2009|
|Previous Article:||Turning budgeting pain into budgeting gain.|
|Next Article:||Entering and editing data using customized forms.|