Excel Sum Records Based On Multiple Criteria Using Array Formulas

To follow this example type the following data starting in A1 and using a separate column for each field.
Branch Type Balance
Brighton Current 5698
Lewes Savings 78878
London ISA 7865
Brighton Savings 45321
Lewes ISA 12356
Brighton Current 8765
London Savings 98345
Brighton ISA 76234
Brighton Savings 3244
London ISA 789876
Brighton Current 781990

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))}

By: Chester Tugwell

Article Directory: http://www.articledashboard.com

Chester Tugwell is a freelance Microsoft Office trainer and owner of Blue Pecan Computer Training based in Sussex, UK. He provides a comprehensive set of Excel training courses as well as other Microsoft Office training options. More free computer training materials are available via the Blue Pecan website.