Conditional Sums Using Multiple Criteria

The examples in the preceding section all use a single comparison criterion. The examples in this section involve summing cells based on multiple criteria. Because the SUMIF function does not work with multiple criteria, you need to use an array formula or the SUMIFS function (for Excel 2007 only). Figure 7-15 shows the sample worksheet again, for your reference.

Figure 7-15: This worksheet demonstrates summing based on multiple criteria.

New 

The SUMIFS function can be used to sum a range when multiple conditions are met. The first argument of SUMIFS is the range to be summed. The remaining arguments are 1 to 127 range/criterion pairs that determine which values in the sum range are included. In the following examples, alternatives to SUMIFS are presented for those workbooks that are required to work in versions prior to 2007.

Using And Criteria

Suppose you want to get a sum of both the invoice amounts that are past due as well as associated with the Oregon office. In other words, the value in the Amount range will be summed only if both of the following criteria are met:

• The corresponding value in the Difference range is negative.

• The corresponding text in the Office range is Oregon.

The SUMIFS function was designed for just this task:

 =SUMIFS(Amount,Difference,"<0",Office,"Oregon") 

In SUMIFS, the first argument is the range to be summed. The remaining arguments define the criteria and come in pairs. Each pair consists of the criteria range followed by the criteria.

For use with earlier versions of Excel, following array formula also does the job:

 {=SUM((Difference<0)*(Office="Oregon")*Amount)} 

This formula creates two new arrays (in memory):

• A Boolean array that consists of TRUE if the corresponding Difference value is less than zero; FALSE otherwise

• A Boolean array that consists of TRUE if the corresponding Office value equals Oregon; FALSE otherwise

Multiplying Boolean values results in the following:

 TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * FALSE = 0 

Therefore, the corresponding Amount value returns non-zero only if the corresponding values in the memory arrays are both TRUE. The result produces a sum of the Amount values that meet the specified criteria.

Note 

You may think that you can rewrite the previous array function as follows, using the SUMPRODUCT function to perform the multiplication and addition:

     =SUMPRODUCT((Difference<0),(Office="Oregon"),Amount) 

For some reason, the SUMPRODUCT function does not handle Boolean values properly, so the formula does not work. The following formula, which multiplies the Boolean values by 1, does work:

 =SUMPRODUCT(1*(Difference<0),1*(Office="Oregon"),Amount) 

Using Or Criteria

Suppose you want to get a sum of past-due invoice amounts, or ones associated with the Oregon office. In other words, the value in the Amount range will be summed if either of the following criteria is met:

• The corresponding value in the Difference range is negative.

• The corresponding text in the Office range is Oregon.

The following array formula does the job:

 {=SUM(IF((Office="Oregon")+(Difference<0),1,0)*Amount)} 

A plus sign (+) joins the conditions; you can include more than two conditions.

Using And and Or Criteria

As you might expect, things get a bit tricky when your criteria consists of both And and Or operations. For example, you may want to sum the values in the Amount range when both of the following conditions are met:

• The corresponding value in the Difference range is negative.

• The corresponding text in the Office range is Oregon or California.

Notice that the second condition actually consists of two conditions, joined with Or. Using multiple SUMIFS can accomplish this:

 =SUMIFS(Amount,Difference,"<0",Office,"Oregon") +SUMIFS(Amount,Difference,"<0",Office,"California") 

The following array formula also does the trick:

 {=SUM((Difference<0)*((Office="Oregon")+(Office="California"))*(Amount))}