"How To Excel" Mini-Tutorials by TheExcelAddict.com "Helping Average Spreadsheet Users Become Local Spreadsheet Experts" Sum Only Data That Meets A Condition You Specify If you have been using Excel for any length of time, I'm sure you have had a need to sum data in a list that meets a specific condition. For example, let's say you use a spreadsheet to record your expenses, and you categorize your expenses such as Food, Entertainment, Utilities, etc.. Let's assume that your table contains the following information: column A = Date, column B = Cheque #, column C = Category, column D = Amount, and column E = Details. Somewhere outside your table you want to track how much you are spending in each category. The solution you are looking for can be found with Excel's SUMIF function. The structure (or syntax) of SUMIF is = SUMIF(RangeToEvaluate,Criteria,RangeToSum) When you use SUMIF, you are telling Excel to look in the RangeToEvaluate for data that matches your Criteria, and for each cell that meets the Criteria, add up the corresponding cells in the RangeToSum and put the result in this cell. In our example table, to calculate the total of all your Entertainment expenses, somewhere outside your table you could use the formula =SUMIF(C1:C50,"Entertainment",D1:D50). For each cell in range C1:C50 (the Category column) that contains the word Entertainment, the function would add the corresponding cell in range D1:D50 (the Amount column). The total of your SUMIF formula would be your total Entertainment expenses. Also, instead of typing the Criteria right into the function, you could use a reference to a cell that contains your criteria so you could easily change your criteria without touching the formula. For example =SUMIF(C1:C50,G2,D1:D50) If you will be copying this formula to other cells, you will need to make your RangeToEvaluate and RangeToSum cell references absolute. That is, they will not adjust when they are copied. In the Formula Bar, highlight the C1:C50 and press the F4 key until there is a dollar sign in front of both the row and column references (i.e. $C$1:$C$50). Repeat for D1:D50. Now your formula should look like =SUMIF($C$1:$C$50,G2,$D$1:$D$50) 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)