Presentation Transcript
Excel SUMIFS Function :www.bluepecan.co.uk Excel SUMIFS Function Microsoft Office Training
Excel Training
Slide 2:www.bluepecan.co.uk SUMIFS is a function that first appears in Excel 2007. You may have previously used SUMIF to sum up values in a list based on criteria in another column. SUMIFS works the same way but allows you to use multiple criteria either in different columns or the same column.
Slide 3:www.bluepecan.co.uk Take our example. Here we have a list of bank accounts in three different branches: Brighton, London and Lewes. For each account we also have information on what type of account it is and then finally the account balance.
We are going to use SUMIFS to calculate the total amount held in current accounts at the Brighton branch.
Slide 4:www.bluepecan.co.uk To follow this example type the following data starting in A1 and using a separate column for each field.
Slide 5:www.bluepecan.co.uk Now enter the following:
Click in cell E1 and typeBranch
Click cell F1 and typeType
Click in cell G1 and type
Total
Slide 6:www.bluepecan.co.uk Click in cell E2 and type
Brighton
Click in cell F2 and type
Current
Slide 7:www.bluepecan.co.uk Click in cell G2 – this is where we will use our SUMIFS function
Click on the fx (Insert Function) button on the formula bar
Type SUMIFS into the search box and click Go
Click on OK
Slide 8:www.bluepecan.co.uk In the Sum Range box type c2:c12This is the range of cells we want to add up
In the Criteria_range1 box type a2:a12This is the range for our branch criteria
In the Criteria1 box type Brighton
In the Criteria_range2 box type b2:b12This is the range for our account type criteria
In the Criteria2 box type Current
Click on OK and you should get the answer 79645
Slide 9:www.bluepecan.co.uk You might want to try specifying two criteria on the same column. For example sum up balances for all accounts held in Brighton and Lewes.
Also try adding more than two criteria, say two criteria for one column and an additional criteria for another.
For those of you who use the COUNTIF function try out the COUNTIFS function in Excel 2007. Like SUMIFS this allows you to specify more than one criteria to count on.
Slide 10:www.bluepecan.co.uk See this Excel training tutorial on the Blue Pecan website