Microsoft Office Courses, Microsoft Office Course Microsoft Office Training, Horsham, Brighton, Sussex, Surrey, Hampshire, London
 
 
 
  tel: 0800 612 4105 email: info@bluepecan.co.uk  

 

Using Excel Sumif with Multiple Criteria
(pre Excel 2007)

 

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

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.


 

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

 

 
Related Links:   

 

   
   

 

 

 

 

Copyright (c) 2012 Blue Pecan. All rights reserved. Sitemap