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
Security Software Basic
By: Margaret Atwood | 10/12/2009Security software is an essential tool to keep intruders by bay from your arrangement. This software helps to care for the assets of your company. Inside justification of remote arrangement, this software allows single the legitimate users to access the arrangement of the company in the least. Computer systems in your company need to be protected with antispyware and antivirus applications.
Copy Xbox 360 Games - Why You Should – You Will Save Money And You Will Save Your Games – Easy Backup Wizard The Software That You Was Looking For
By: Dimitar Mishev | 10/12/2009Each and every month video game prices are increasing; this is down to two main factors. The first main factor is that the big games are coming out at this time of the year. Very high profile sequels are hitting the market. Assassins Creed is getting an anticipated sequel while Call of Duty Modern Warfare 2 is the most anticipated of all time.
Backup Xbox 360 Games in 10 Minutes Or Less – Backup your Games With Easy Backup Wizard
By: Dimitar Mishev | 10/12/2009Many gamers from various forums I visit on the internet are looking for the same answer as you, how can you backup your Xbox 360 games, and how can you do it fast? Well keep reading this article and I will tell you.
Copy and Play Xbox 360 Games in Minutes – Save Your Disc And Save Money – Easy Backup Wizard
By: Dimitar Mishev | 10/12/2009You may think you have read the title wrong but I assure you not, and it is in fact possible to copy and play your favorite Xbox 360 games in minutes. If you're a hardcore gamer that loves to play games for hours and hours on end then this is just what you have been waiting for. I have been playing games in the past for hours at a time for them to all of a sudden stop working for no apparent reason
Easy Backup Wizard - Xbox 360 Game Copying Software – Best Way To Save Your Games
By: Dimitar Mishev | 10/12/2009This article will discuss Easy Backup Wizard. For people out there who may not k now what this is I will explain, so keep on reading this article. Easy Backup Wizard is special software that allows gamers to copy their own video games to protect them from damage or in case they lose their favorite possessions. So this article will let you know how this product stands up against the rest of the market.
The Number One Xbox 360 Game Copying Software of 2009 – Save Money And Save Your Games - Easy Backup Wizard
By: Dimitar Mishev | 10/12/2009If you have been living in a cave or had your head in a shed recently then you may not have heard about Easy Backup Wizard, however don't worry let me bring you back to the present. Easy Backup Wizard is software that allows you to copy your own video games. It can copy games from various consoles such as the Xbox 360, Playstation 3, Playstation 2, Nintendo Wii, PC and even the original Xbox console.
Protect Your Xbox 360 Games by Copying Them – Save money on Your Games – Make Backup On Your Game
By: Dimitar Mishev | 10/12/2009There's nothing worse in this whole world to a gamer to destroy one of their computer games, actually there is. There's nothing worse than to be three quarters of the way through a game and then your game stops working.
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.
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 Forms
By: Chester Tugwell | 18/06/2009 | DatabasesA selection of tips for working with Microsoft Access Forms. 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.