Excel Zen Spreadsheet Blog

Excel Formula: Summing with Multiple Criteria Part 2

In Part 1 we looked at the following array formula for summing cells in the range A1:A1000 which are less than 50 or greater than 100

=SUM(IF((A1:A1000>100)+(A1:A1000<50),A1:A1000,0))

This array formula is quite specific so we need to look for a more general array formula that acts like SUMIF but which accepts more than one criterion. What we want to come up with is a generalized array formula like the one below:

=SUM(IF(criteria, range_to_sum, 0))

At this point it’s a good idea to remember that we’re dealing with an array formula. Array formulas need to be entered by pressing the SHIFT, CTRL and ENTER keys together.

We want to be able to link criteria together using logical operators such as “and” and “or”. We do this by using addition whenever we want to do a logical “or” operation and by using multiplication whenever we want to do a logical “and” operation. This might seem strange because Excel provides the logical functions AND and OR which ought to do the job. Unfortunately both of these worksheet functions return a single value rather than an array of values when they are used inside an array formula.

So looking at the generalized array formula above we can re-write criteria as

c₁ op₁ c₂ op₂ c₃ op₃ ….

where

c_x is one of the criteria we are using to determine whether or not a cell is included in the sum (x = 1, 2, 3 …)

and

op_x is either + or * depending on whether we want an “or” operator or an “and” operator respectively (x = 1, 2, 3 …)

range_to_sum is the range over which the sum is taken.

You can use brackets to control the order in which the criteria are evaluated if you wish.

This looks rather complicated but a few examples will make things clearer:

Example 1

=SUM(IF(((A1:A1000>100)+(A1:A1000<50))*(MOD(A1:A1000,5)=0),A1:A1000,0))

This array formula sums cells in the range A1:A1000 that are either less than 50 or greater than 100 and which are also a multiple of 5. To see how this works let’s break down the first parameter in the IF function:

The MOD function divides by 5 and calculates the remainder. If the remainder is 0, the value is a multiple of 5.

So this array formula checks each cell in the range A1:A1000 to see whether it is greater than 100 or less than 50 and if so, it checks whether it is a multiple of 5. If a value satisfies these criteria, the IF function returns that value and otherwise it returns 0. The SUM function adds all values returned by the IF to get the final result.

Example 2

=SUM(IF((A1:A1000>100)+(A1:A1000<50)+(MOD(A1:A1000,5)=0),A1:A1000,0))

This array formula sums cells in the range A1:A1000 that are either less than 50 or greater than 100 or a multiple of 5.

Example 3

=SUM(IF((A1:A1000<100)*(A1:A1000>50)*(MOD(A1:A1000,5)=0),A1:A1000,0))

This array formula sums cells in the range A1:A1000 that are greater than 50 and less than 100 and a multiple of 5.

These examples apply criteria to a given range and then sum that same range. There is no reason why we can’t apply criteria to one range and then sum a different range. There is no problem using different ranges so long as the ranges have the same number of rows. Let’s look at an example. Suppose we want to sum those cells in the range A1:A1000 where cells in the same row satisfy these two conditions:

• the value of the cell in column B is greater than zero
• the text in the column C cell is “February”

We can find the answer easily by using an array formula like this:

=SUM(IF((B1:B1000>0)*(C1:C1000=”February”),A1:A1000,0))

Finally, some of you may be wondering why I haven’t mentioned the SUMIFS worksheet function. There are two main reasons why I haven’t talked about this function. The first reason is that the SUMIFS function is only available if you’re using Excel 2007. If you’re using an earlier version of Excel then this function isn’t available. The second reason is that the SUMIFS function only adds together cells when all criteria are met. You cannot, for example, use SUMIFS directly to calculate the sum of all values that satisfy two out of three criteria. In this regard using SUMIFS is not as flexible as the array formula approach.

Tags: array formula, conditional sum, excel formula, excel function, SUMIF worksheet function, SUMIFS worksheet function