"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
Once you see how it works
- 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*.
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 ( There are three parts to this formula. - The first part of the formula tells Excel which range of cells you want to evaluate (B:B).
- The next part is the Criteria (cell G2 containing the value North) which you are asking Excel to look for in the evaluated range.
- 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
Why not print (CTRL+P) this tip and share it with your friends and associates?
| 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) |

otherwise click here and you'll find lots more time-saving Excel tutorials like this one.