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.
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.
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
Now enter the following:
Click in cell E1 and type
Branch
Click cell F1 and type
Type
Click in cell G1 and type
Total
Click in cell E2 and type
Brighton
Click in cell F2 and type
Current
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
In the Sum Range box type c2:c12
This is the range of cells we want to add up
In the Criteria_range1 box type a2:a12
This is the range for our branch criteria
In the Criteria1 box type Brighton
In the Criteria_range2 box type b2:b12
This is the range for our account type criteria
In the Criteria2 box type Current
Click on OK and you should get the answer 796453
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.
- Related Videos
- Related Articles
- Ask / Related Q&A




How to Convert a PSD into PNG File
By: Jenith Dsouza | 09/11/2009Images, pictures, designs etc., are ready with you, mostly in Photoshop as PSD files? When you try opening these files, you will be able to continue working on the images again. But if you want to extract some of the layers from psd file without loosing the transparency of the images, then here is the solution.
Outsource Software Development: The New Era of Profitability
By: Arun Kumar | 09/11/2009The entire world of software development has seen a whale of change in the last few years. With the arrival of a host of major software development firms, clients have started getting the most unique and advanced software programs that can increase productivity thus resulting in profit maximization. The software developers have used the most innovative software development platforms and they have just hit it straight in help their clients get what they exactly want.
ECommerce Programming for Microsoft Dynamics GP
By: Andrew Karasev | 08/11/2009Microsoft Dynamics GP is open to ecommerce software developers through such tools and SDK as eConnect, GP Integration Manager. Plus you can further customize ecommerce shopping cart transformation to Dynamics GP Sales Order Processing Invoice in Microsoft Dexterity (Dexterity triggers) or in MS SQL Server Level (SQL Insert or Update triggers).
Iphoto Library
By: Rose Broyles | 08/11/2009When you upload your photos into iPhoto, they are stored in a library folder where the software organizes and keeps track of their precise locations. Certain data files are also created to help in their organization. These are also stored in the same folder as the photos.
Zygors Guides - Leveling Guide For World of Warcraft
By: Jon | 08/11/2009Is it possible to level up 80 levels in seven days on WoW? I will be honest with you...
Iphoto '09
By: Rose Broyles | 08/11/2009Of all the photo archive and basic editing porgrams, iPhoto is one of the easiest if not easiest. Apple has always strived to make all of their products whether hardware or software user-friendly and iPhoto is an example of this. iPhoto is a software application designed by Apple, Inc. as a photo management and archival system that is efficient, effective, and user-friendly. The iPhoto was first issued in 2002. Several versions have been released since then, the last being iPhoto '09. This was u
Project Green Button Review
By: Folusho Orokunle | 08/11/2009Project Green Button, created by Marc Lindsay and Daniel Turner, is an all-in-one desktop-based software designed to create, optimize and promote websites.
Grab A Traffic Technic That Promise Online Success|Mass Article Control Review
By: Felicita Vazquez | 07/11/2009With Mass Article Creator, you build hundreds or even thousands of high-quality, relevant articles with a click or two with your mouse Mass Article Submitter will submit those freshly created articles to the top article directories for you AUTOMATICALLY.
Creating a User Defined Function in Excel using VBA
By: Chester Tugwell | 10/07/2009 | SoftwareExcel includes many different functions that help you complete calculations, but have you ever wished for a function that that doesn’t exist in Excel? If you have, this tutorial will explain how to create a function DIY style.
Excel SUMIFS Function
By: Chester Tugwell | 03/07/2009 | SoftwareSUMIFS 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.
Create A Drop Down List In Excel
By: Chester Tugwell | 03/07/2009 | SoftwareDrop down lists are a great addition to a worksheet making data more efficient and also restricting what values can be entered into a cell.
Excel Mortgage Payment Formula
By: Chester Tugwell | 22/06/2009 | SoftwareA walk through of the PMT function in Excel which allows you to calculate monthly repayments on a mortgage.
Tips and Tricks for working with Microsoft Access Reports
By: Chester Tugwell | 18/06/2009 | DatabasesA selection of tips and tricks for working in Microsoft Access reports. Not all tips will be relevant to all versions of Access
Tips and Tricks for working with Microsoft Access Queries
By: Chester Tugwell | 18/06/2009 | DatabasesA collection of tips and tricks that you may find useful when working with queries in Microsoft Access.
Displaying Parameter Values in a Microsoft Access Report
By: Chester Tugwell | 18/06/2009 | DatabasesIf you use parameter queries and then base reports on those queries this tutorial is for you. It will show you to display the parameter values that have been used in the heading of the report.