Excel Zen Spreadsheet Blog

Excel Formula: Summing with Multiple Criteria Part 1

Excel provides a useful way of summing cells that satisfy a given criterion. The SUMIF worksheet function is designed specifically for this purpose.

By way of example, let’s suppose you want to sum the cells in the range A1:A1000 which have a value greater than 9.95. You can do this easily by using the formula

=SUMIF(A1:A1000,”>9.95”)

The parameters used by the SUMIF function are fairly self-explanatory. The first parameter is the range to be summed and the second is the criterion that determines whether or not a cell is included in the sum.

One failing of the SUMIF function is that it can’t cope with multiple criteria. For example, you might want to sum cells which are between two given values. Unfortunately SUMIF can’t do this for you. In this series of posts we’ll look at a way of working around this limitation.

The approach we are going to take is to use an array formula which combines the SUM worksheet function and the IF worksheet function. An array formula performs multiple calculations and returns either a single value or multiple values. You can distinguish an array formula from an ordinary formula by looking at Excel’s formula bar – an array formula is enclosed by braces (curly brackets) like this {}. To enter an array formula you type in the formula as normal but instead of pressing the ENTER key you press the ENTER, SHIFT and CTRL keys simultaneously.

The screenshot above shows the array formula

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

This array formula calculates the sum of all cells in range A1:A1000 which are greater than 100 or less than 50. The IF function is calculated repeatedly – one time for each cell in the range A1:A1000. If a cell in this range is greater than 100 or less than 50 then the IF function returns the cell value and otherwise it returns 0. These values are then passed to the SUM function which calculates the desired result.

The following screenshot shows the array formula in action:

You can see how the IF function returns an array that is a subset of the range A1:A1000, where cells that are between 50 and 100 (inclusive) have been replaced with 0.

It is important to remember that we are working with an array formula. If you enter the formula by pressing the ENTER key the formula will most probably give you the wrong answer. You need to enter the formula by pressing the ENTER, SHIFT and CTRL keys together.

The formula we’ve looked at here is rather specific. It needs to be generalized so that you can adapt it to meet your own needs. In part 2 we’ll look at extending the formula to cover a more general class of criteria.

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