Free Microsoft Excel Tutorial - Excel SUMIFS in Excel 2003

Microsoft Office Training > Excel Training > Free Computer Training >Excel Sum Records Based on Multiple Criteria Using Array Formulas

Please feel free to use this content on your web site, but please include the links at the end

Excel Sum Records Based on Multiple Criteria Using Array Formulas

Excel SUMIFS in Excel 2003

If you are frustrated with the Excel SUMIF function because it only allows you to specify a single criteria, you have two options:  one would be to purchase Excel 2007 and use the new SUMIFS function and the other would be to use an array function in Excel 2003 or earlier.

To follow this example type the following data starting in A1 and using a separate column for each field.

We are going to calculate the sum of balance in current accounts held at the Brighton branch. 

To give you an idea of how array formulas work we will start off by performing a single criteria sum by using a combination of an If function and Sum function. A SUMIF function would perform the same calculation.

In cell E1 type the following formula =SUM(IF(A2:A12="brighton",C2:C12,0)), but use CTRL, SHIFT, ENTER to confirm the formula.  This will put brace brackets around your formula like this: {=SUM(IF(A2:A12="brighton",C2:C12,0))}.  The brace brackets indicate an array formula. This formula calculates that there is £921,252 held at the Brighton branch.

So how do we consider multiple criteria?  Well we need to nest another IF statement inside our current one so that we only up the values if both criteria are true. 

Here is the formula: {=SUM(IF(A2:A12="brighton",IF(B2:B12="current",C2:C12,0),0))}

Microsoft Office Training with Blue Pecan Computer Training. Tailored Microsoft Excel Training. Microsoft Excel Training Surrey