"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

How To Sum Values Based On A Condition


Everyone is familiar with Excel's SUM worksheet function but the lesser known SUMIF function may be just what you need in certain situations.

Once you see how it works you'll probably think of many other uses for it. So I'll give you an example and explain what it does. To really understand this you should do this example on your own worksheet.

  • Let's say you have a table containing sales data.
  • In column A you have your Dates.
  • In column B you have your Sales Regions: North, South, East and West
  • In column C you have your Sales Figures.

How can you get a total of the sales by Region without messing around with your original data?

Here's what you can do:

Somewhere outside of your table, enter the regions (North, South, East, West) into four cells (let's use cells G2:G5 for this example). Now, in cell H2 enter the formula =SUMIF(B:B,G2,C:C) and copy that formula to the other three cells.

There are three parts to this formula.

  1. The first part of the formula tells Excel which range of cells you want to evaluate (B:B).
  2. The next part is the Criteria (cell G2 containing the value North) which you are asking Excel to look for in the evaluated range.
  3. Finally, the third part of this formula tells Excel what range (C:C) to use to find the related values whenever your criteria is met.

Here's how the formula works. The formula looks down Column B and for every value in that range that matches your Criteria, it adds the corresponding value from Column C to the SUMIF total. So if the value in cell B12 is North, the corresponding amount from cell D12 is added to the SUMIF total. The SUMIF total will be the total of all records in the table matching your criteria.

Note that you can use other ranges of cells - you don't have to use whole columns. Also, you can enter criteria directly into your formula, for example "<500".


Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.