Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Adding a Little Animation to Your Life
Converting a Range of URLs to Hyperlinks
Making the Formula Bar Persistent
Johan wrote about a problem he was having with the SUMIF function in his worksheet. It seems that when the data in his worksheet changes, the SUMIF function doesn't automatically update. He is, however, able to update the formula if he selects it, presses F2 (to jump into edit mode) and then press Enter.
The first, easiest, and most obvious thing to check is whether you have automatic calculation turned on. Follow these steps if you are using a version of Excel prior to Excel 2007:
If you are using Excel 2007 then you should follow these steps, instead:
If this does not fix the problem, then it must be related to the actual SUMIF formula. For instance, you may think that the data you are changing in the worksheet is actually referenced in the SUMIF formula, but it may not be. (If it isn't, then changing the data won't change the formula results.) Check the formula to make sure that it covers the proper range.
Remember, as well, that if your worksheet is large and complex, and the SUMIF formula is in a cell that is above your data table, it is possible that you may need two calculation passes to update the SUMIF function. Why? Because when you calculate a worksheet, Excel basically calculates the cells from left to right and top to bottom. If you have a very large worksheet, with lots of dependent calculations, and the calculations on which everything else is dependent are at the bottom or right side of the worksheet, then you may get incorrect results for the SUMIF function. (This happens only with the most complex of worksheets.) The answer is to reorganize your worksheets so that the primary calculations are placed near the top of the worksheet and as far left as possible, and the calculations that are based on those primary calculations are placed later in the worksheet.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3069) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Save Time! ExcelTips has been published weekly since late 1998. Past issues of ExcelTips are available in convenient ExcelTips archives. Have your own enhanced archive of ExcelTips at your fingertips, available to use at any time!