Excel SUMIFS Function

<h1>Excel SUMIFS Function</h1>Author: <a title="Chester Tugwell" href="http://www.articlesbase.com/authors/chester-tugwell/169941.htm">Chester Tugwell</a> 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.About the Author: 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 <a href="http://www.bluepecan.co.uk/microsoft_excel_courses.htm">Excel training</a> courses as well as other <a href="http://www.bluepecan.co.uk">Microsoft Office training</a> options. More <a href="http://www.bluepecan.co.uk/free_computer_training.htm">free computer training materials</a> are available via the Blue Pecan website.Article Source: <a href="http://www.articlesbase.com/">ArticlesBase.com</a> - <a href="http://www.articlesbase.com/software-articles/excel-sumifs-function-1012304.html" title="Excel SUMIFS Function">Excel SUMIFS Function</a>

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

Re-Publish

Article Source: http://www.articlesbase.com/software-articles/excel-sumifs-function-1012304.html

Article Tags: Sumifs Excel

Related Videos
Related Articles
Ask / Related Q&A

Play

How to use DATEDIF Excel Function

Play

SUBSTITUTE and REPLACE Excel Functions Tutorial

Play

How to Use the SUMIF Command in Microsoft Excel 2007

Play

Introducing the SUMIF Command in Microsoft Excel 2007

Play

Learn Excel from MrExcel Episode 900 - SUMPRODUCT SUMIFS

Latest Software Articles
More from Chester Tugwell

How to Convert a PSD into PNG File

By: Jenith Dsouza | 09/11/2009
Images, 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/2009
The 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/2009
Microsoft 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/2009
When 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/2009
Is 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/2009
Of 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/2009
Project 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/2009
With 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 | 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.