Remember Me
forgot your password?

Excel SUMIFS Function

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.

Chester Tugwell

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.

Rate this Article: 0 / 5 stars - 0 vote(s)
Print Email Re-Publish

Add new Comment



Captcha

  • Latest Software Articles
  • More from Chester Tugwell

Security Software Basic

By: Margaret Atwood | 10/12/2009
Security 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/2009
Each 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/2009
Many 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/2009
You 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/2009
This 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/2009
If 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/2009
There'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 | Software
Excel 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 | Software
Drop 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 | Software
A 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 | Databases
A 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 | Databases
A 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 | Databases
A 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 | Databases
If 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.

Submit Your Articles Free: Signup
Article Categories




Use of this web site constitutes acceptance of the Terms Of Use and Privacy Policy | User published content is licensed under a Creative Commons License.
Copyright © 2005-2008 Free Articles by ArticlesBase.com, All rights reserved. (0.04, 0, w1)